MySQL基础知识
MySQL相关的网址
官网主页：https://www.oracle.com/mysql/index.html
下载主页面：https://www.mysql.com/downloads
社区资源下载页：https://dev.mysql.com/downloads/
MySQL社区版下载页面https://dev.mysql.com/downloads/mysql/
MySQL相关产品介绍
Oracle MySQL Cloud Service (commercial)
商业付费软件，基于MySQL企业版和Oracle云服务提供企业级 的MySQL数据库服务
MySQL Enterprise Edition(commercial)
商业付费软件，除了提供MySQL数据库服务之外，包含了connector(程序连接接口)，partition(表分区)，企业级的monitor(监控)，HA(高可用)，backup(备份)，Scalability(扩展)等
MySQL Cluster CGE(commercial)
商业付费软件，基于MySQL Cluster和企业版拥有的各项功能提供企业级的高并发 、高吞吐的数据库服务
MySQL Community Edition
免费社区软件，提供基础的数据库服务和其他衍生服务
SQL语句规范
SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。
在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持sql。
<1> 在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写，表名库名小写；
<2> SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。
<3> 用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。
SELECT * FROM tb_table WHERE NAME="YUAN";
<4> 注释：单行注释：--
多行注释：/*......*/
<5>sql语句可以折行操作
数值类型
字符串类型
存储字符串：
CHAR系列 ：CHAR VARCHAR
TEXT系列 ： TINYTEXT TEXT MEDIUMTEXT LONGTEXT
存储二进制数据：
BINARY系列： BINARY VARBINARY
BLOB 系列 ： TINYBLOB BLOB MEDIUMBLOB LONGBLOB
日期类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
SQL语法基础
数据库操作
Create database语句
mysql> create database pys18; Query OK, 1 row affected (0.04 sec)
再次创建数据库失败
mysql> create database pys18; ERROR 1007 (HY000): Can't create database 'pys18'; database exists
创建数据库之前先判断数据库是否存在避免报错
mysql> create database if not exists pys18; Query OK, 1 row affected, 1 warning (0.00 sec)
查看数据库创建方式
mysql> show create database pys18; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | pys18 | CREATE DATABASE `pys18` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
修改数据库字符集
mysql> alter database pys18 character set utf8; Query OK, 1 row affected (0.00 sec)
切换数据库
mysql> use pys18 Database changed
所有的MySQL语法都可以通过help命令查看
mysql> help create database Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use t statement, you need the CREATE privilege for the database. CREAT SCHEMA is a synonym for CREATE DATABASE. URL: http://dev.mysql.com/doc/refman/5.7/en/create-database.html
数据表操作
Create table语句
CREATE TABLE tab_name( field1 type[完整性约束条件], field2 type, ... fieldn type )[character set xxx];
mysql> create table student(sid int,sname varchar(10)); Query OK, 0 rows affected (0.57 sec)
CREATE TABLE employee( id int primary key auto_increment , name varchar(20), gender bit default 1, birthday date, department varchar(20), salary double(8,2) unsigned, resume text );
查看表信息
desc tab_name 查看表结构 show columns from tab_name 查看表结构 show tables 查看当前数据库中的所有的表 show create table tab_name 查看当前数据库表建表语句
修改表结构-增加列（字段）
-- (1)增加列(字段) alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］; #添加多个字段 alter table users2 add addr varchar(20), add age int first, add birth varchar(20) after name; -- (2)修改一列类型 alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］; -- (3)修改列名 alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］; -- (4)删除一列 alter table tab_name drop [column] 列名;-- (5)修改表名 rename table 表名 to 新表名; -- (6)修该表所用的字符集 alter table student character set utf8;
mysql> alter table score add teacher_id int after course_id; Query OK, 0 rows affected (0.95 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table score add class_id int, -> add rname varchar(10), -> add stab varchar(10) after teacher_id; Query OK, 0 rows affected (0.77 sec)
删除表
mysql> drop table student; Query OK, 0 rows affected (0.24 sec)
指定在pys18数据库里面创建表语句
mysql> create table pys18.teacher(sid int,sname varchar(10)); Query OK, 0 rows affected (0.38 sec)
创建表之前判断表是否存在，避免语句执行错误
mysql> create table if not exists student(sid int,sname varchar(10)); Query OK, 0 rows affected, 1 warning (0.00 sec)
Temporary关键词表示创建的是临时表，临时表仅对本链接可见，另外的数据库链接不可见，当本链接断开时，临时表也自动被drop掉
mysql> create temporary table temp1(sid int,sname varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into temp1 values(1,'xiewenming'); Query OK, 1 row affected (0.02 sec) mysql> select * from temp1; +------+------------+ | sid | sname | +------+------------+ | 1 | xiewenming | +------+------------+ 1 row in set (0.00 sec)
Like关键词表示基于另外一个表的定义复制一个新的空表，空表上的字段属性和索引都和原表相同
mysql> create table students_copy like student; Query OK, 0 rows affected (0.45 sec)
Create table … as select语句表示创建表的同时将select的查询结果数据插入到表中，但索引和主外键信息都不会同步过来
(Ignore和replace表示在插入数据的过程中如果新表中碰到违反唯一约束的情况下怎么处理，ignore表示不插入，replace表示替换已有的数据，默认两个关键词都不写则碰到违反的情况会报错)
mysql> create table students_copy2 as select * from temp1; Query OK, 1 row affected (0.37 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc students_copy2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | sid | int(11) | YES | | NULL | | | sname | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from students_copy2; +------+------------+ | sid | sname | +------+------------+ | 1 | xiewenming | +------+------------+ 1 row in set (0.00 sec)
Not null/null表示字段是否允许为空，默认为null表示允许为空，not null表示需要对此字段明确数值，或者要有默认值，否则报错
mysql> create table course(cid int not null,cname varchar(10) Query OK, 0 rows affected (0.34 sec) mysql> insert into course(cname) values('English'); ERROR 1364 (HY000): Field 'cid' doesn't have a default value
Default表示设置字段的默认值
mysql> create table class(cid int,cname varchar(10),teacher_id int default 1); Query OK, 0 rows affected (0.28 sec) mysql> insert into class(cid,cname) values(2,"English"); Query OK, 1 row affected (0.11 sec) mysql> select * from class; +------+---------+------------+ | cid | cname | teacher_id | +------+---------+------------+ | 2 | English | 1 | +------+---------+------------+ 1 row in set (0.00 sec)
Auto_increment表示字段为整数或者浮点数类型的value+1递增数值，value为当前表中该字段最大的值，默认是从1开始递增；一个表中只容许有一个自增字段，且该字段必须有key属性，不能含有default属性，且插入负值会被当成很大的正数
mysql> create table score(sid int auto_increment,course_id int, num int); ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum n and it must be defined as a key
mysql> create table score(sid int primary key auto_increment,course_id int, num int); Query OK, 0 rows affected (0.35 sec) mysql> insert into score(course_id,num) values(1,90); Query OK, 1 row affected (0.07 sec) mysql> insert into score(course_id,num) values(2,96); Query OK, 1 row affected (0.07 sec) mysql> insert into score(sid,course_id,num) values(5,2,96); Query OK, 1 row affected (0.04 sec) mysql> insert into score(course_id,num) values(6,99); Query OK, 1 row affected (0.09 sec) mysql> select * from score; +-----+-----------+------+ | sid | course_id | num | +-----+-----------+------+ | 1 | 1 | 90 | | 2 | 2 | 96 | | 5 | 2 | 96 | | 6 | 6 | 99 | +-----+-----------+------+ 4 rows in set (0.00 sec)
Primary key表示该字段为主键，主键字段必须唯一，必须非空，一个表中只能有一个主键，主键可以包含一个或多个字段
表记录操作
insert语句三种写法的常规用法
<1>插入一条记录： insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......); <2>插入多条记录： insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......)， (value1,value2,.......), ... ; <3>set插入: insert ［into］ tab_name set 字段名=值 */
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | sid | int(11) | YES | | NULL | | | sname | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into student values(1,"xiewenming"); Query OK, 1 row affected (0.07 sec) mysql> insert into student set sid=2,sname="warren"; Query OK, 1 row affected (0.11 sec) mysql> select * from student; +------+------------+ | sid | sname | +------+------------+ | 1 | xiewenming | | 2 | warren | +------+------------+ 2 rows in set (0.00 sec) mysql> create table students like student; Query OK, 0 rows affected (0.36 sec) mysql> select * from students; Empty set (0.00 sec) mysql> insert into students select * from student; Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from students; +------+------------+ | sid | sname | +------+------------+ | 1 | xiewenming | | 2 | warren | +------+------------+ 2 rows in set (0.00 sec) mysql> insert into student set sid=3,sname="yuan"; Query OK, 1 row affected (0.04 sec) mysql> insert into students select * from student; Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from students; +------+------------+ | sid | sname | +------+------------+ | 1 | xiewenming | | 2 | warren | | 1 | xiewenming | | 2 | warren | | 3 | yuan | +------+------------+ 5 rows in set (0.00 sec)
INSERT employee (name,gender,birthday,salary,department) VALUES ("alex",1,"1985-12-12",8000,"保洁部"), ("egon",1,"1987-08-08",5000,"保安部"), ("yuan",1,"1990-06-06",20000,"教学部"); INSERT employee VALUES (8,"女神",0,"1992-02-12","教学部",7000,""); INSERT employee SET name="wusir",birthday="1990-11-11";
其中insert…values和insert…set两种语句都是将指定的数据插入到现成的表中，而insert…select语句是将另外表中数据查出来并插入到现成的表中
删除表记录
mysql> delete from score where sid=1; Query OK, 1 row affected (0.09 sec)
如果不加where会删除表中所有记录
mysql> delete from score; Query OK, 4 rows affected (0.04 sec)
修改表记录
update tab_name set field1=value1,field2=value2,......[where 语句] --示例 将yuan的薪水在原有基础上增加1000元。 update employee_new set salary=salary+4000 where name='yuan';
mysql> select * from students; +------+------------+ | sid | sname | +------+------------+ | 1 | xiewenming | | 2 | warren | | 1 | xiewenming | | 2 | warren | | 3 | yuan | +------+------------+ 5 rows in set (0.00 sec) mysql> update students set sname="oldboy",sid=10 where sname="xiewenming"; Query OK, 2 rows affected (0.07 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from students; +------+--------+ | sid | sname | +------+--------+ | 10 | oldboy | | 2 | warren | | 10 | oldboy | | 2 | warren | | 3 | yuan | +------+--------+ 5 rows in set (0.00 sec)
查询表记录(select)
-- 查询语法： SELECT *|field1,filed2 ... FROM tab_name WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 -- Mysql在执行sql语句时的执行顺序： -- from where select group by having order by
准备表和数据
CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), gender ENUM("male","female","other"), age TINYINT, dep VARCHAR(20), city VARCHAR(20), salary DOUBLE(7,2) ); INSERT INTO emp (name,gender,age,dep,city,salary) VALUES ("yuan","male",24,"教学部","河北省",8000), ("egon","male",34,"保安部","山东省",8000), ("alex","male",28,"保洁部","山东省",10000), ("景丽阳","female",22,"教学部","北京",9000), ("张三", "male",24,"教学部","河北省",6000), ("李四", "male",32,"保安部","北京",12000), ("王五", "male",38,"教学部","河北省",7000), ("赵六", "male",19,"保安部","河北省",9000), ("猪七", "female",24,"保洁部","北京",9000); SELECT * FROM emp;
mysql> SELECT * FROM emp; +----+-----------+--------+------+-----------+-----------+----------+ | id | name | gender | age | dep | city | salary | +----+-----------+--------+------+-----------+-----------+----------+ | 1 | yuan | male | 24 | 教学部 | 河北省 | 8000.00 | | 2 | egon | male | 34 | 保安部 | 山东省 | 8000.00 | | 3 | alex | male | 28 | 保洁部 | 山东省 | 10000.00 | | 4 | 景丽阳 | female | 22 | 教学部 | 北京 | 9000.00 | | 5 | 张三 | male | 24 | 教学部 | 河北省 | 6000.00 | | 6 | 李四 | male | 32 | 保安部 | 北京 | 12000.00 | | 7 | 王五 | male | 38 | 教学部 | 河北省 | 7000.00 | | 8 | 赵六 | male | 19 | 保安部 | 河北省 | 9000.00 | | 9 | 猪七 | female | 24 | 保洁部 | 北京 | 9000.00 | +----+-----------+--------+------+-----------+-----------+----------+ rows in set (0.00 sec)
where字句 过滤查询
-- where字句中可以使用： -- 比较运算符： > < >= <= <> != between 80 and 100 值在10到20之间 in(80,90,100) 值是10或20或30 like 'yuan%' /* pattern可以是%或者_， 如果是%则表示任意多字符，此例如唐僧,唐国强 如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__ */ -- 逻辑运算符 在多个条件直接可以使用逻辑运算符 and or not
示例
-- 查询年纪大于24的员工 SELECT * FROM emp WHERE age>24; -- 查询教学部的男老师信息 SELECT * FROM emp WHERE dep="教学部" AND gender="male";
order：按指定的列进行，排序的列即可是表中的列名，也可以是select语句后指定的别名。
-- 语法： select *|field1,field2... from tab_name order by field [Asc|Desc] -- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
示例
-- 按年龄从高到低进行排序 SELECT * FROM emp ORDER BY age DESC ; -- 按工资从低到高进行排序 SELECT * FROM emp ORDER BY salary;
GROUP BY 语句根据某个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。
-- 语法: SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
示例
-- 查询男女员工各有多少人 SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender; -- 查询各个部门的人数 SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep; -- 查询每个部门最大的年龄 SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep; -- 查询每个部门年龄最大的员工姓名 SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep); -- 查询每个部门的平均工资 SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep; -- 查询教学部的员工最高工资: SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部"; -- 查询平均薪水超过8000的部门 SELECT dep,AVG(salary) FROM emp GROUP BY dep HAVING avg(salary)>8000; -- 查询每个组的员工姓名 SELECT dep,group_concat(name) FROM emp GROUP BY dep; -- 查询公司一共有多少员工(可以将所有记录看成一个组) SELECT COUNT(*) 员工总人数 FROM emp;
limit记录条数限制
SELECT * from ExamResult limit 1; SELECT * from ExamResult limit 2,5; -- 跳过前两条显示接下来的五条纪录 SELECT * from ExamResult limit 2,2;
正则表达式
SELECT * FROM employee WHERE emp_name REGEXP '^yu'; SELECT * FROM employee WHERE emp_name REGEXP 'yun$'; SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
多表查询
创建表及数据
CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), salary DOUBLE(7,2), dep_id INT ); INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2), ("李四",12000,1), ("王五",5000,2), ("赵六",8000,3), ("猪七",9000,1), ("周八",7000,4), ("蔡九",7000,2); CREATE TABLE dep( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); INSERT INTO dep (name) VALUES ("教学部"), ("销售部"), ("人事部");
笛卡尔积查询
mysql> select * from emp,dep; +----+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 1 | 教学部 | | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 1 | 张三 | 8000.00 | 2 | 3 | 人事部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 2 | 李四 | 12000.00 | 1 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 3 | 人事部 | | 3 | 王五 | 5000.00 | 2 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 3 | 王五 | 5000.00 | 2 | 3 | 人事部 | | 4 | 赵六 | 8000.00 | 3 | 1 | 教学部 | | 4 | 赵六 | 8000.00 | 3 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 5 | 猪七 | 9000.00 | 1 | 2 | 销售部 | | 5 | 猪七 | 9000.00 | 1 | 3 | 人事部 | | 6 | 周八 | 7000.00 | 4 | 1 | 教学部 | | 6 | 周八 | 7000.00 | 4 | 2 | 销售部 | | 6 | 周八 | 7000.00 | 4 | 3 | 人事部 | | 7 | 蔡九 | 7000.00 | 2 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | | 7 | 蔡九 | 7000.00 | 2 | 3 | 人事部 | +----+--------+----------+--------+----+-----------+ rows in set (0.00 sec)
内连接 查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
-- SELECT * FROM emp,dep WHERE emp.dep_id=dep.id; SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id; +----+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | +----+--------+----------+--------+----+-----------+ rows in set (0.00 sec) -- 已上2条语句结果是相同的
这时，我们就可以利用两张表中所有的字段进行查询了
示例
-- 查询李四所在的部门名称 SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name="李四"; -- 查询销售部所有员工姓名以及部门名称 -- SELECT name FROM emp WHERE dep_id in (SELECT id FROM dep WHERE name="销售部"); SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name="销售部";
外连接
--（1）左外连接：在内连接的基础上增加左边有右边没有的结果 SELECT * FROM emp LEFT JOIN dep ON dep.id=emp.dep_id;
+----+--------+----------+--------+------+-----------+
| id | name | salary | dep_id | id | name |
+----+--------+----------+--------+------+-----------+
| 2 | 李四 | 12000.00 | 1 | 1 | 教学部 |
| 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 |
| 1 | 张三 | 8000.00 | 2 | 2 | 销售部 |
| 3 | 王五 | 5000.00 | 2 | 2 | 销售部 |
| 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 |
| 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 |
| 6 | 周八 | 7000.00 | 4 | NULL | NULL |
+----+--------+----------+--------+------+-----------+
rows in set (0.00 sec)
--（1）外右连接：在内连接的基础上增加右边有左边没有的结果 SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id; mysql> SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id; +------+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +------+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | +------+--------+----------+--------+----+-----------+ rows in set (0.00 sec)
完整性约束
完整性约束是对字段进行限制，从而符合该字段达到我们期望的效果比如字段含有默认值，不能是NULL等 。直观点说：如果插入的数据不满足限制要求，数据库管理系统就拒绝执行操作
唯一约束
唯一约束可以有多个但索引列的值必须唯一，索引列的值允许有空值。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE。
CREATE TABLE t5( id INT AUTO_INCREMENT, name VARCHAR(20) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY UK_t5_name (name) ); -- 建表后添加约束: alter table t5 add constraint UK_t5_name unique (name); -- 如果不需要唯一约束，则可以这样删除 ALTER TABLE t5 DROP INDEX UK_t5_name;
添加约束和删除约束
-- 创建唯一约束: create unique index UK_t5_name on t5 (name); -- 建表后添加约束: alter table t5 add constraint UK_t5_name unique (name); -- 如果不需要唯一约束，则可以这样删除 ALTER TABLE t5 DROP INDEX UK_t5_name;
自增约束
MySQL 每张表只能有1个自动增长字段，这个自动增长字段通常作为主键，也可以用作非主键使用，但是请注意将自动增长字段当做非主键使用时必须必须为其添加唯一索引，否则系统将会报错。
mysql> CREATE TABLE t4( -> id INT NOT NULL, -> name VARCHAR(20), -> age INT AUTO_INCREMENT -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
可以改为
mysql> CREATE TABLE t4( -> id INT NOT NULL, -> name VARCHAR(20), -> age INT UNIQUE AUTO_INCREMENT -> ); Query OK, 0 rows affected (0.13 sec)
主键约束
主键是用于唯一标识一条记录的约束，如同身份证。
主键有两个约束：非空且唯一！
创建主键
-- 方式1 CREATE TABLE t1( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); -- 方式2 CREATE TABLE t2( id INT NOT NULL, name VARCHAR(20) );
注意：
1、一张表中最多只能有一个主键
2、表中如果没有设置主键，默认设置NOT NULL的字段为主键；此外，表中如果有多个NOT NULL的字段，则按顺序将第一个设置NOT NULL的字段设为主键。
结论：主键一定是非空且唯一，但非空且唯一的字段不一定是主键。
3、主键类型不一定必须是整型
添加主键和删除主键
-- 添加主键alter table tab_name add primary key(字段名称,...) -- 删除主键alter table users drop primary key;
注意，如果主键是AUTO_INCREMENT，需要先取消AUTO_INCREMENT，因为AUTO_INCREMENT只能加在KEY上。
CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT); -- 思考，如何删除主键？ ALTER TABLE test modify id int; -- auto_increment没了，但这样写主键依然存在，所以还要加上下面这句 ALTER TABLE test drop primary key;-- 仅仅用这句也无法直接删除主键
复合主键
所谓的复合主键 就是指你表的主键含有一个以上的字段。
如果一列不能唯一区分一个表里的记录时，可以考虑多个列组合起来达到区分表记录的唯一性，形式
创建时：
create table sc ( studentid int, courseid int, score int, primary key (studentid,courseid) );
修改时：
alter table tb_name add primary key (字段1,字段2,字段3);
外键约束
外键语法
-- 外键的定义语法： [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法 可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
准备表和数据
-- 子表 CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), dep_id INT -- CONSTRAINT emp_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id) -- 注意外键字段的数据类型必须与关联字段一致 ); -- 主表 CREATE TABLE dep( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); INSERT emp (name,dep_id) VALUES ("alex",1), ("egon",2), ("alvin",2), ("莎莎",1), ("wusir",2), ("女神",2), ("冰冰",3), ("姗姗",3); INSERT dep (name) VALUES ("市场部"), ("教学部"), ("销售部");
添加外键
现在，删除市场部：
mysql> DELETE FROM dep WHERE name="市场部"; Query OK, 1 row affected (0.01 sec)
居然删除成功了，不可思议，现在问题来了： alex和莎莎两个人怎么办？
所以，为了避免类似操作，我们需要给两张表建立约束，这种约束称为外键约束。外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作
INSERT dep (id,name) VALUES (1,"市场部"); -- 思考为什么加这一句? ALTER TABLE emp ADD CONSTRAINT dep_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id); mysql> DELETE FROM dep WHERE name="市场部"; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fail
INNODB支持的ON语句
外键约束对子表的含义: 如果在主表中(比如dep)找不到候选键,则不允许在子表(比如emp)上进行insert/update
外键约束对父表的含义: 在主表上进行update/delete以更新或删除在子表中有一条或多条应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的 -- on update/on delete子句
-- ------------------------innodb支持的四种方式--------------------------------- cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除 FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE; set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null ; 要注意子表的外键列不能为not null FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL; Restrict方式 :拒绝对父表进行删除更新操作(了解) No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 ; 进行update/delete操作（了解）
表的关系（两张表）
一对多：将关联字段设置在多的表中
CREATE TABLE emp( id INT PRIMARY KEY , name VARCHAR (20), dep_id INT , FOREIGN KEY (dep_id) REFERENCES dep(id) )
多对多：借助第三表实现
CREATE TABLE STUDENT2TEACHER( id INT PRIMARY KEY auto_increment, studnet_id INT , teacher_id INT , FOREIGN KEY (studnet_id) REFERENCES student(id), FOREIGN KEY (teacher_id) REFERENCES teacher(id), )
一对一：将关联字段设为Unique
CREATE TABLE author( id INT PRIMARY KEY , name VARCHAR (20), authorDetial_id INT unique, FOREIGN KEY (dep_id) REFERENCES dep(id) )
参考:http://www.cnblogs.com/yuanchenqi/articles/7269675.html
志不强者智不达
浙公网安备 33010602011771号