SQL语法基础
Create database语句
- Create database语句是在MySQL实例上创建一个指定名称的数据 库，create schema语句的语义和create database是一样的
- 当创建的数据库本身存在而且没有写明if not exists子句时，则创 建数据库的语句会报错
Create database语句
- create_specification子句指明创建的数据库的属性，并存储在db.opt文 件中
- Character set属性指明此数据库的默认字符集
- Collate属性指明此数据库的默认排序规则
- 创建后的数据库在数据文件所在目录会创建一个自己的文件目录，用 来包含后续创建的表文件
-
也可以直接通过mkdir在数据目录创建目录， 则MySQL会识别为一个数据库，并在执行show databases命令时可以看 到
mysql> create database test3; -- 创建数据库成功 Query OK, 1 row affected (0.00 sec) mysql> create database test3; -- 再次创建数据库失败 ERROR 1007 (HY000): Can't create database 'test3'; database exists mysql> create database if not exists test3; -- 语句执行成功 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use test3; -- 切换到test3数据库
create table语句
- tbl_name表示被创建的表名，默认在当前数据库下创建此表，当 然也可以指定在某个数据库下创建表
-
if not exists表示当相同的表名存在时，则不执行此创建语句，避 免语句执行错误
create table students2(sid int,sname varchar(10)); create table test3.students2(sid int,sname varchar(10)); --在test3这个数据库下创建students2表 create table if not exists students2(sid int,sname varchar(10));
Temporary关键词
-
表示创建的是临时表，临时表仅对本链接可见，另外的数据库链接不可见， 当本链接断开时，临时表也自动被drop掉
mysql> create temporary table temp1(sid int,sname varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into temp1 values(1,'a'); Query OK, 1 row affected (0.00 sec) mysql> select * from temp1; +------+-------+ |sid |sname| +------+-------+ | 1 |a | +------+-------+ 1 row in set (0.00 sec) -
另一个数据库链接执行相同的查询语句查不到数据
mysql> select * from temp1; ERROR 1146 (42S02): Table 'test.temp1' doesn't exist -- 本数据库链接断开后再链接，临时表也不存在 mysql> select * from temp1; ERROR 1146 (42S02): Table 'test.temp1' doesn't exist
Like关键词
-
表示基于另外一个表的定义复制一个新的空表，空表上的字段属性和索 引都和原表相同
mysql>create table students_copy like students; Query OK, 0 rows affected (0.01 sec) mysql> show create table students_copy; CREATE TABLE `students_copy` ( `sid` int(11) DEFAULT NULL, `sname` varchar(20) DEFAULT NULL, `sex` int(11) DEFAULT NULL, UNIQUE KEY `idx_st_sid` (`sid`), KEY `idx_st_union` (`sname`,`sex`) )ENGINE=InnoDB DEFAULT CHAR SET=latin1
Create table ... as select语句
- 表示创建表的同时将select的查询结果数据 插入到表中，但索引和主外键信息都不会同步过来
mysql> create table students_copy2 as select * from students;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students_copy2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL
| sex | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+Ignore和replace
- 表示在插入数据的过程中如果新表中碰到违反唯一约束 的情况下怎么处理
- ignore表示不插入，replace表示替换已有的数据， 默认两个关键词都不写则碰到违反的情况会报错
Data_type
- 表示定义的字段类型
Not null/null
- 表示字段是否允许为空，默认为null表示允许为空，
- not null表示需要对此字段明确数值，或者要有默认值，否则报错
mysql> create table students2(sid int not null,sname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into students2(sname) values('eee');
ERROR 1364 (HY000): Field 'sid' doesn't have a default valueDefault表示设置字段的默认值
create table students3(sid int,sname varchar(10),gender int default 0);
insert into students3 values(1,'a',default);
insert into students3(sid,sname) values(2,'b');
mysql> select * from students3;
+------+-------+--------+
|sid |sname |gender |
+------+-------+--------+
|1|a|0|
|2|b|0|Auto_increment
- 表示字段为整数或者浮点数类型的value+1递增数值，value为当前表 中该字段最大的值，默认是从1开始递增
-
一个表中只容许有一个自增字段,且该字段必须有key属性,不能含有default属性，且插入负值会被当成很大的正数
mysql>create table students4(sid int auto_increment,sname varchar(10)); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql>create table students4(sid int primary key auto_increment,sname varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into students4(sname) values('a'); Query OK, 1 row affected (0.00 sec) mysql> insert into students4(sid,sname) values(3,'b'); Query OK, 1 row affected (0.01 sec) mysql> insert into students4(sname) values('c'); Query OK, 1 row affected (0.00 sec) mysql> select * from students4; +-----+-------+ |sid |sname | +-----+-------+ |1|a| |3|b| |4|c|
Constraint
-
表示为主键、唯一键、外键等约束条件命名，如果没有命名则MySQL会默认给一个
Primary key
- 表示该字段为主键，主键字段必须唯一，必须非空，
-
一个表中只能有一个主键，主键可以包含一个或多个字段
Key/index
-
表示索引字段
Unique
-
表示该字段为唯一属性字段，且允许包含多个null值
Foreign key
表示该字段为外键字段
CREATE TABLE `gender` (
gender_id int(11) NOT NULL,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (gender_id)
)；
create table students5(sid int not null primary key auto_increment,sname varchar(10) unique,gender int,constraint for_1 foreign key (gender) references gender(gender_id));设计选课数据库系统
- 创建一个名为course的数据库
- 在该数据库下创建以下几个表：
- Students表：sid整型自增主键，sname字符串64位，gender字符串12位，dept_id整型并外键到dept表的id字段
- Dept表：id整型自增主键，dept_name字符串64位
- Course表：id整型自增字段主键，course_name字符串64位，teacher_id整型外键到teacher表的id字段
- Teacher表：id整型自增字段主键，name字符串64位，dept_id整型外键到dept表的id字段
- Students表和teacher表的dept_id为非空
mysql> create database course;
mysql> use course;
mysql> create table dept(id int auto_increment primary key,dept_name varchar(64));
mysql> create table students(sid int auto_increment primary key,sname varchar(54),gender varchar(12),dept_id int not null,constraint for_1 foreign key(dept_id) references dept(id));
mysql> create table teacher(id int auto_increment primary key,name varchar(64),dept_id int not null, constraint for_2 foreign key(dept_id) references dept(id));
mysql> create table course(id int auto_increment primary key,course_name varchar(64),teacher_id int,constraint for_3 foreign key(teacher_id) references teacher(id));