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 value

Default表示设置字段的默认值

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));