|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
[root@lamp byrd]# /usr/local/mysql/bin/mysql -uroot -p mysql> create database byrd default character set utf8 collate utf8_general_ci; #建立一个byrd的数据库且字符集为utf8 mysql> show create database byrd; #查看建立数据库的命令 +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | byrd | CREATE DATABASE `byrd` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show databases like 'byrd'; #查询数据库是否存在 +-----------------+ | Database (byrd) | +-----------------+ | byrd | +-----------------+ 1 row in set (0.08 sec) mysql> use byrd #进入byrd数据库 Database changed mysql> create table test( #建立一个叫做test的数据库 -> id int(4) not null primary key auto_increment, #插入int类型，int为整数类型存储4个字节，数据不为空，设置主键，id自动增加 -> name char(20) not null #char 定长字符串类型，varchar 变长字符串类型，不为空 -> ); Query OK, 0 rows affected (1.61 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, #自增 `name` char(20) NOT NULL, PRIMARY KEY (`id`) #主键 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show tables ; +----------------+ | Tables_in_byrd | +----------------+ | test | +----------------+ 1 row in set (0.00 sec) mysql> insert into test(id,name) values(1,'zy'); Query OK, 1 row affected (0.16 sec) mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | +----+------+ 1 row in set (0.00 sec) [root@lamp byrd]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B byrd >/tmp/tmp-B.sql [root@lamp byrd]# egrep -v "^\-|\*|^$" /tmp/tmp-B.sql USE `byrd`; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gbk; LOCK TABLES `test` WRITE; INSERT INTO `test` VALUES (1,'zy'),(2,'zpf'),(3,'three'),(4,'zpf'),(5,'five'); UNLOCK TABLES; |
索引相关：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
mysql> show columns from test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | +-------+----------+------+-----+---------+----------------+ mysql> show tables; #查询所有表 +----------------+ | Tables_in_byrd | +----------------+ | test | +----------------+ mysql> drop table test; #删除test表 mysql> create table test( -> id int(4) not null primary key auto_increment, -> name char(20) not null, -> KEY `index_name` (`name`) #index_name是索引 -> ); mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`), #主键，唯一 KEY `index_name` (`name`) #索引可以是多个 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | +-------+----------+------+-----+---------+----------------+ mysql> alter table test drop index index_name; #删除索引 mysql> show columns from test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | +-------+----------+------+-----+---------+----------------+ mysql> show create table test; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ mysql> alter table test add index index_name (name); #在name建立索引index_name mysql> create index index_name on test(name); #在name建立索引index_name,不可以创建主键索引 mysql> show index from test\G *************************** 1. row *************************** Table: test Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: test Non_unique: 1 Key_name: index_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> alter table test add PRIMARY KEY(id); #增加主键 mysql> alter table test DROP PRIMARY KEY; #删除主键 mysql> help alter table; #相关帮助 mysql> select * from test where id=1; +----+-------+ | id | name | +----+-------+ | 1 | byrd1 | | 1 | byrd1 | | 1 | byrd1 | +----+-------+ mysql> insert into test values(1,'byrd10') mysql> insert into test values(2,'byrd2'),(3,'byrd3'); mysql> delete from test where id =1 and name='byrd10'; |
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
