04-MySQL-SQL基础语法

1、create database 语句

1.1、create_specification子句指明创建的数据库的属性，并存储在db.opt文件中

 1 -- 创建数据库
 2 mysql> show databases like 'test3';
 3 +------------------+
 4 | Database (test3) |
 5 +------------------+
 6 | test3            |
 7 +------------------+
 8 
 9 -- 查看创建的数据库
10 mysql> show databases like 'test3';
11 +------------------+
12 | Database (test3) |
13 +------------------+
14 | test3            |
15 +------------------+
16 
17 -- 创建数据库
18 mysql> create database  if not exists test3;
19 Query OK, 1 row affected, 1 warning (0.01 sec)
20 
21 -- 切换数据库
22 mysql> use test3
23 Database changed

2、create table 语句

2.1、Tbl_name表示被创建的表名，默认在当前数据库下创建此表，当然也可以指定在某个数据库下创建表

2.2、If not exists表示当相同的表名存在时，则不执行此创建语句，避免语句执行错误

  1 -- 创建表
  2 mysql> create table student(sid int,sname varchar(10));
  3 Query OK, 0 rows affected (0.12 sec)
  4 
  5 -- 查看创建的表
  6 mysql> show tables;
  7 +-----------------+
  8 | Tables_in_test3 |
  9 +-----------------+
 10 | student         |
 11 +-----------------+
 12 1 row in set (0.00 sec)
 13 
 14 -- 查看创建表的语句
 15 mysql> show create table student;
 16 +---------+--------------------------------------------------------------------------------------------------------------------------------+
 17 | Table   | Create Table                                                                                                                   |
 18 +---------+--------------------------------------------------------------------------------------------------------------------------------+
 19 | student | CREATE TABLE `student` (
 20   `sid` int(11) DEFAULT NULL,
 21   `sname` varchar(10) DEFAULT NULL
 22 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 23 +---------+--------------------------------------------------------------------------------------------------------------------------------+
 24 1 row in set (0.01 sec)
 25 
 26 -- If not exists表示当相同的表名存在时，则不执行此创建语句，避免语句执行错误
 27 mysql> show create table student;
 28 +---------+--------------------------------------------------------------------------------------------------------------------------------+
 29 | Table   | Create Table                                                                                                                   |
 30 +---------+--------------------------------------------------------------------------------------------------------------------------------+
 31 | student | CREATE TABLE `student` (
 32   `sid` int(11) DEFAULT NULL,
 33   `sname` varchar(10) DEFAULT NULL
 34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 35 +---------+--------------------------------------------------------------------------------------------------------------------------------+
 36 1 row in set (0.01 sec)
 37 
 38 -- Temporary关键词表示创建的是临时表，临时表仅对本链接可见，另外的数据库链接不可见，当本链接断开时，临时表也自动被drop掉
 39 mysql> create temporary table  tmp1(sid  int,sname varchar(10));
 40 Query OK, 0 rows affected (0.03 sec)
 41 
 42 mysql> insert into tmp1(sid,sname) values(1,'alex');
 43 Query OK, 1 row affected (0.02 sec)
 44 
 45 mysql> select * from tmp1;
 46 +------+-------+
 47 | sid  | sname |
 48 +------+-------+
 49 |    1 | alex  |
 50 +------+-------+
 51 1 row in set (0.00 sec)
 52 
 53 mysql> use test3;
 54 Database changed
 55 mysql>
 56 mysql>
 57 mysql> show tables;
 58 +-----------------+
 59 | Tables_in_test3 |
 60 +-----------------+
 61 | student         |
 62 +-----------------+
 63 1 row in set (0.02 sec)
 64 
 65 -- Like关键词表示基于另外一个表的定义复制一个新的空表，空表上的字段属性和索引都和原表相同
 66 mysql> create table student_1 like student;
 67 Query OK, 0 rows affected (0.03 sec)
 68 
 69 mysql> show tables;
 70 +-----------------+
 71 | Tables_in_test3 |
 72 +-----------------+
 73 | student         |
 74 | student_1       |
 75 +-----------------+
 76 2 rows in set (0.00 sec)
 77 
 78 -- Create table … as select语句表示创建表的同时将select的查询结果数据插入到表中，但索引和主外键信息都不会同步过来
 79 
 80 mysql> create table students_copy_1 as select * from students_copy;
 81 Query OK, 0 rows affected (0.03 sec)
 82 Records: 0  Duplicates: 0  Warnings: 0
 83 
 84 mysql> desc students_copy;
 85 +-------+-------------+------+-----+---------+-------+
 86 | Field | Type        | Null | Key | Default | Extra |
 87 +-------+-------------+------+-----+---------+-------+
 88 | sid   | int(11)     | YES  | UNI | NULL    |       |
 89 | sname | varchar(20) | YES  | MUL | NULL    |       |
 90 | sex   | int(11)     | YES  |     | NULL    |       |
 91 +-------+-------------+------+-----+---------+-------+
 92 3 rows in set (0.01 sec)
 93 
 94 mysql> desc students_copy_1;
 95 +-------+-------------+------+-----+---------+-------+
 96 | Field | Type        | Null | Key | Default | Extra |
 97 +-------+-------------+------+-----+---------+-------+
 98 | sid   | int(11)     | YES  |     | NULL    |       |
 99 | sname | varchar(20) | YES  |     | NULL    |       |
100 | sex   | int(11)     | YES  |     | NULL    |       |
101 +-------+-------------+------+-----+---------+-------+
102 3 rows in set (0.00 sec)
103 
104 mysql> show create table students_copy_1;
105 +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
106 | Table           | Create Table                                                                                                                                                                              |
107 +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
108 | students_copy_1 | CREATE TABLE `students_copy_1` (
109   `sid` int(11) DEFAULT NULL,
110   `sname` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
111   `sex` int(11) DEFAULT NULL
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
113 +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
114 1 row in set (0.00 sec)
115 
116 mysql> show create table students_copy;
117 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
118 | Table         | Create Table                                                                                                                                                                                                                                  |
119 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
120 | students_copy | CREATE TABLE `students_copy` (
121   `sid` int(11) DEFAULT NULL,
122   `sname` varchar(20) DEFAULT NULL,
123   `sex` int(11) DEFAULT NULL,
124   UNIQUE KEY `idx_st_sid` (`sid`),
125   KEY `idx_st_union` (`sname`,`sex`)
126 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
127 +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
128 1 row in set (0.00 sec)
129 
130 -- Ignore和replace表示在插入数据的过程中如果新表中碰到违反唯一约束的情况下怎么处理，ignore表示不插入，replace表示替换已有的数据，默认两个关键词都不写则碰到违反的情况会报错
131 
132 -- Data_type表示定义的字段类型，后续会有详细介绍
133 -- Not null/null表示字段是否允许为空，默认为null表示允许为空，not null表示需要对此字段明确数值，或者要有默认值，否则报错
134 
135 mysql> create table student_2(sid int not null,sname varchar(10));
136 Query OK, 0 rows affected (0.05 sec)
137 
138 mysql> show create table student_2;
139 +-----------+------------------------------------------------------------------------------------------------------------------------------+
140 | Table     | Create Table                                                                                                                 |
141 +-----------+------------------------------------------------------------------------------------------------------------------------------+
142 | student_2 | CREATE TABLE `student_2` (
143   `sid` int(11) NOT NULL,
144   `sname` varchar(10) DEFAULT NULL
145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
146 +-----------+------------------------------------------------------------------------------------------------------------------------------+
147 1 row in set (0.00 sec)
148 
149 mysql> insert into student_2(sid,sname) values(1,'alex');
150 Query OK, 1 row affected (0.03 sec)
151154 mysql> show create table student;
155 +---------+--------------------------------------------------------------------------------------------------------------------------------+
156 | Table   | Create Table                                                                                                                   |
157 +---------+--------------------------------------------------------------------------------------------------------------------------------+
158 | student | CREATE TABLE `student` (
159   `sid` int(11) DEFAULT NULL,
160   `sname` varchar(10) DEFAULT NULL
161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
162 +---------+--------------------------------------------------------------------------------------------------------------------------------+
163 1 row in set (0.00 sec)
164 
165 mysql> insert into student(sname) values('jack');
166 Query OK, 1 row affected (0.02 sec)
167 
168 -- Default表示设置字段的默认值
169 mysql> create table student_3(sid int,sname varchar(10),gender int default 0);
170 Query OK, 0 rows affected (0.05 sec)
171 
172 mysql> insert into student_3 values(1,'bob');
173 ERROR 1136 (21S01): Column count doesn't match value count at row 1
174 mysql> insert into student_3 values(1,'bob',2);
175 Query OK, 1 row affected (0.02 sec)
176 
177 mysql>
178 mysql> insert into student_3(sid,sname) values(1,'jack');
179 Query OK, 1 row affected (0.01 sec)
180 
181 mysql> select * from student_3;
182 +------+-------+--------+
183 | sid  | sname | gender |
184 +------+-------+--------+
185 |    1 | bob   |      2 |
186 |    1 | jack  |      0 |
187 +------+-------+--------+
188 2 rows in set (0.00 sec)
189 
190 -- Auto_increment表示字段为整数或者浮点数类型的value+1递增数值，value为当前表中该字段最大的值，默认是从1开始递增；一个表中只容许有一个自增字段，且该字段必须有key属性，不能含有default属性，且插入负值会被当成很大的正数
191 
192 mysql> create table student_4(sid int auto_increment,sname varchar(10));
193 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
194 mysql> create table student_4(sid int primary key auto_increment,sname varchar(10));
195 Query OK, 0 rows affected (0.03 sec)
196 
197 mysql> show create table student_4;
198 +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
199 | Table     | Create Table                                                                                                                                                       |
200 +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
201 | student_4 | CREATE TABLE `student_4` (
202   `sid` int(11) NOT NULL AUTO_INCREMENT,
203   `sname` varchar(10) DEFAULT NULL,
204   PRIMARY KEY (`sid`)
205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
206 +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
207 1 row in set (0.00 sec)
208 
209 mysql> insert into student_4(sname) values('zs');
210 Query OK, 1 row affected (0.02 sec)
211 
212 mysql> insert into student_4(sname) values('ls');
213 Query OK, 1 row affected (0.01 sec)
214 
215 mysql> insert into student_4(sname) values('ww');
216 Query OK, 1 row affected (0.00 sec)
217 
218 mysql> insert into student_4(sname) values('zl');
219 Query OK, 1 row affected (0.02 sec)
220 
221 mysql> select * from student_4;
222 +-----+-------+
223 | sid | sname |
224 +-----+-------+
225 |   1 | zs    |
226 |   2 | ls    |
227 |   3 | ww    |
228 |   4 | zl    |
229 +-----+-------+
230 4 rows in set (0.00 sec)
231 
232 /*
233 Column_format 目前仅在ndb存储引擎的表上有用，表示该字段的存储类型是fixed, dynamic或者是default
234 
235 Storage 目前也仅在ndb存储引擎的表上有用Constraint表示为主键、唯一键、外键等约束条件命名，如果没有命名则MySQL会默认给一个
236 
237 Primary key 表示该字段为主键，主键字段必须唯一，必须非空，一个表中只能有一个主键，主键可以包含一个或多个字段
238  
239 Key/index 表示索引字段
240 
241 Unique 表示该字段为唯一属性字段，且允许包含多个null值
242 Foreign key表示该字段为外键字段
243 */