#显示所有的数据库
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | db_utf | | mysql | | performance_schema | | test | +--------------------+
#创建数据库
MariaDB [(none)]> create database s18; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | db_utf | | mysql | | performance_schema | | s18 | | test | +--------------------+
#查看数据库的创建信息
MariaDB [(none)]> show create database s18; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | s18 | CREATE DATABASE `s18` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+ MariaDB [(none)]> create database s18_2 character set utf8; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | db_utf | | mysql | | performance_schema | | s18 | | s18_2 | | test | +--------------------+ MariaDB [(none)]> show create database s18_2; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | s18_2 | CREATE DATABASE `s18_2` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+
#修改数据库字符集编码
MariaDB [(none)]> alter database s18 character set utf8; Query OK, 1 row affected (0.01 sec)
#删除数据库
MariaDB [(none)]> drop database s18_2; Query OK, 0 rows affected (0.00 sec)
#使用数据库
MariaDB [(none)]> use s18; Database changed
#查看当前使用的数据库
MariaDB [s18]> select database(); +------------+ | database() | +------------+ | s18 | +------------+
数据表操作
#创建表
MariaDB [s18]> create table c1( -> name varchar(20), -> age int); Query OK, 0 rows affected (0.01 sec)
#查看表结构
MariaDB [s18]> desc c1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
#查看所有字段
MariaDB [s18]> select * from c1; Empty set (0.00 sec)
#查看当前数据库下的所有表
MariaDB [s18]> show tables; +---------------+ | Tables_in_s18 | +---------------+ | c1 | +---------------+
#查看数据库的创建表信息
MariaDB [s18]> show create table c1G *************************** 1. row *************************** Table: c1 Create Table: CREATE TABLE `c1` ( `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
#添加新字段
MariaDB [s18]> alter table c1 add salary float NOT NULL; Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> desc c1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | salary | float | NO | | NULL | | +--------+-------------+------+-----+---------+-------+
完整性约束
1.主键约束
primary key:非空(not null)且唯一(unique)
MariaDB [s18]> alter table c1 add id int first; Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> desc c1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | salary | float | NO | | NULL | | +--------+-------------+------+-----+---------+-------+
#修改字段
MariaDB [s18]> alter table c1 modify id int primary key auto_increment first; Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> desc c1; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | salary | float | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+
#更改表名
MariaDB [s18]> alter table c1 change name names varchar(20); Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> desc c1; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | names | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | salary | float | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+
#删除表字段
MariaDB [s18]> alter table c1 drop names; Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> desc c1; +--------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | age | int(11) | YES | | NULL | | | salary | float | NO | | NULL | | +--------+---------+------+-----+---------+----------------+
#删除表
drop table table_name;
表记录操作
MariaDB [s18]> create table emp( -> id int primary key auto_increment, -> name varchar(20), -> gender tinyint, -> bir date, -> salary float(6,2), -> dep varchar(20)); Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> desc emp; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | gender | tinyint(4) | YES | | NULL | | | bir | date | YES | | NULL | | | salary | float(6,2) | YES | | NULL | | | dep | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+
1.增加表记录
MariaDB [s18]> insert emp (id,name,gender,bir,salary,dep) values -> (1,'tom',1,'1989-12-12',99.99,'安保部'); MariaDB [s18]> select * from emp; +----+------+--------+------------+--------+-----------+ | id | name | gender | bir | salary | dep | +----+------+--------+------------+--------+-----------+ | 1 | tom | 1 | 1989-12-12 | 99.99 | 安保部 | +----+------+--------+------------+--------+-----------+ MariaDB [s18]> insert emp (id,name,gender,bir,salary,dep) values -> (2,'jerry',0,'1986-12-12',199.99,'安保部'), -> (3,'peter',1,'1988-12-12',2299.99,'高管部'); Query OK, 2 rows affected (0.00 sec) MariaDB [s18]> select * from emp; +----+-------+--------+------------+---------+-----------+ | id | name | gender | bir | salary | dep | +----+-------+--------+------------+---------+-----------+ | 1 | tom | 1 | 1989-12-12 | 99.99 | 安保部 | | 2 | jerry | 0 | 1986-12-12 | 199.99 | 安保部 | | 3 | peter | 1 | 1988-12-12 | 2299.99 | 高管部 | +----+-------+--------+------------+---------+-----------+
#使用set也可以插入记录
MariaDB [s18]> insert emp set name='prog',gender=1,bir='1990-04-12',salary=8000,dep='教学部'; MariaDB [s18]> select * from emp; +----+-------+--------+------------+---------+-----------+ | id | name | gender | bir | salary | dep | +----+-------+--------+------------+---------+-----------+ | 1 | tom | 1 | 1989-12-12 | 99.99 | 安保部 | | 2 | jerry | 0 | 1986-12-12 | 199.99 | 安保部 | | 3 | peter | 1 | 1988-12-12 | 2299.99 | 高管部 | | 4 | prog | 1 | 1990-04-12 | 8000.00 | 教学部 | +----+-------+--------+------------+---------+-----------+
2.修改表记录
MariaDB [s18]> update emp set salary=300 where name='peter'; MariaDB [s18]> select * from emp; +----+-------+--------+------------+---------+-----------+ | id | name | gender | bir | salary | dep | +----+-------+--------+------------+---------+-----------+ | 1 | tom | 1 | 1989-12-12 | 99.99 | 安保部 | | 2 | jerry | 0 | 1986-12-12 | 199.99 | 安保部 | | 3 | peter | 1 | 1988-12-12 | 300.00 | 高管部 | | 4 | prog | 1 | 1990-04-12 | 8000.00 | 教学部 | +----+-------+--------+------------+---------+-----------+
3.删除表记录
MariaDB [s18]> delete from emp where id=2; MariaDB [s18]> select * from emp; +----+-------+--------+------------+---------+-----------+ | id | name | gender | bir | salary | dep | +----+-------+--------+------------+---------+-----------+ | 1 | tom | 1 | 1989-12-12 | 99.99 | 安保部 | | 3 | peter | 1 | 1988-12-12 | 300.00 | 高管部 | | 4 | prog | 1 | 1990-04-12 | 8000.00 | 教学部 | +----+-------+--------+------------+---------+-----------+
#清空表记录
1.delete from table_name
2.truncate table_name --> 适用于数据量非常大的表
MariaDB [s18]> truncate emp; MariaDB [s18]> select * from emp; Empty set (0.00 sec)
4.查询表记录
MariaDB [s18]> drop table emp; MariaDB [s18]> 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)); Query OK, 0 rows affected (0.00 sec) MariaDB [s18]> insert into emp (name,gender,age,dep,city,salary) values -> ('tom','male',24,'教学部','河北省',8000), -> ('jerry','male',34,'保安部','山东省',8000), -> ('peter','male',28,'保安部','山东省',10000), -> ('景丽阳','female',22,'教学部','北京',9000), -> ('张三','male',24,'教学部','河北省',6000), -> ('李四','male',32,'保安部','北京',12000), -> ('王五','male',38,'教学部','河北省',7000), -> ('赵六','male',28,'保安部','河北省',9000), -> ('猪七','female',24,'保洁部','北京',9000); Query OK, 9 rows affected (0.01 sec) MariaDB [s18]> select * from emp; +----+-----------+--------+------+-----------+-----------+----------+ | id | name | gender | age | dep | city | salary | +----+-----------+--------+------+-----------+-----------+----------+ | 1 | tom | male | 24 | 教学部 | 河北省 | 8000.00 | | 2 | jerry | male | 34 | 保安部 | 山东省 | 8000.00 | | 3 | peter | 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 | 28 | 保安部 | 河北省 | 9000.00 | | 9 | 猪七 | female | 24 | 保洁部 | 北京 | 9000.00 | +----+-----------+--------+------+-----------+-----------+----------+ MariaDB [s18]> select name,salary from emp; +-----------+----------+ | name | salary | +-----------+----------+ | tom | 8000.00 | | jerry | 8000.00 | | peter | 10000.00 | | 景丽阳 | 9000.00 | | 张三 | 6000.00 | | 李四 | 12000.00 | | 王五 | 7000.00 | | 赵六 | 9000.00 | | 猪七 | 9000.00 | +-----------+----------+ MariaDB [s18]> select name as 姓名,salary as 薪水 from emp; # as 可以省略 MariaDB [s18]> select name 姓名,salary 薪水 from emp; +-----------+----------+ | 姓名 | 薪水 | +-----------+----------+ | tom | 8000.00 | | jerry | 8000.00 | | peter | 10000.00 | | 景丽阳 | 9000.00 | | 张三 | 6000.00 | | 李四 | 12000.00 | | 王五 | 7000.00 | | 赵六 | 9000.00 | | 猪七 | 9000.00 | +-----------+----------+ MariaDB [s18]> select name from emp where age>20; +-----------+ | name | +-----------+ | tom | | jerry | | peter | | 景丽阳 | | 张三 | | 李四 | | 王五 | | 赵六 | | 猪七 | +-----------+ MariaDB [s18]> select name,age from emp where age>20; +-----------+------+ | name | age | +-----------+------+ | tom | 24 | | jerry | 34 | | peter | 28 | | 景丽阳 | 22 | | 张三 | 24 | | 李四 | 32 | | 王五 | 38 | | 赵六 | 28 | | 猪七 | 24 | +-----------+------+ MariaDB [s18]> select name,age from emp where age between 20 and 30; +-----------+------+ | name | age | +-----------+------+ | tom | 24 | | peter | 28 | | 景丽阳 | 22 | | 张三 | 24 | | 赵六 | 28 | | 猪七 | 24 | +-----------+------+ MariaDB [s18]> select name,age from emp where name like '张%'; +--------+------+ | name | age | +--------+------+ | 张三 | 24 | +--------+------+ MariaDB [s18]> insert emp set name='张无忌'; MariaDB [s18]> select name,age from emp where name like '张%'; +-----------+------+ | name | age | +-----------+------+ | 张三 | 24 | | 张无忌 | NULL | +-----------+------+ 一个_代表一个符号 MariaDB [s18]> select name,age from emp where name like '张_'; +--------+------+ | name | age | +--------+------+ | 张三 | 24 | +--------+------+ 1 row in set (0.00 sec) MariaDB [s18]> select name,age from emp where name like '张__'; +-----------+------+ | name | age | +-----------+------+ | 张无忌 | NULL | +-----------+------+ 1 row in set (0.00 sec) MariaDB [s18]> select name,age from emp where gender='male' and age>25; +--------+------+ | name | age | +--------+------+ | jerry | 34 | | peter | 28 | | 李四 | 32 | | 王五 | 38 | | 赵六 | 28 | +--------+------+
分组查询group by
MariaDB [s18]> delete from emp where name='张无忌'; MariaDB [s18]> select * from emp group by dep; +----+--------+--------+------+-----------+-----------+---------+ | id | name | gender | age | dep | city | salary | +----+--------+--------+------+-----------+-----------+---------+ | 2 | jerry | male | 34 | 保安部 | 山东省 | 8000.00 | | 9 | 猪七 | female | 24 | 保洁部 | 北京 | 9000.00 | | 1 | tom | male | 24 | 教学部 | 河北省 | 8000.00 | +----+--------+--------+------+-----------+-----------+---------+ 此表是显示的每个部门的第一个人的信息，没有用处 MariaDB [s18]> select dep from emp group by dep; +-----------+ | dep | +-----------+ | 保安部 | | 保洁部 | | 教学部 | +-----------+ MariaDB [s18]> select count(name) from emp group by dep; +-------------+ | count(name) | +-------------+ | 4 | | 1 | | 4 | +-------------+ MariaDB [s18]> select count(*) from emp group by dep; # 都可以 使用聚合函数查看每个部门的总的人数 MariaDB [s18]> select dep,count(*) from emp group by dep; +-----------+----------+ | dep | count(*) | +-----------+----------+ | 保安部 | 4 | | 保洁部 | 1 | | 教学部 | 4 | +-----------+----------+ 每个部门具体的人数 MariaDB [s18]> select dep,max(salary) from emp group by dep; +-----------+-------------+ | dep | max(salary) | +-----------+-------------+ | 保安部 | 12000.00 | | 保洁部 | 9000.00 | | 教学部 | 9000.00 | +-----------+-------------+ 查看每个部门的最高工资 MariaDB [s18]> select gender,count(*) from emp group by gender; +--------+----------+ | gender | count(*) | +--------+----------+ | male | 7 | | female | 2 | +--------+----------+ 统计男生和女生的人数 MariaDB [s18]> select city,avg(salary) from emp group by city; +-----------+--------------+ | city | avg(salary) | +-----------+--------------+ | 北京 | 10000.000000 | | 山东省 | 9000.000000 | | 河北省 | 7500.000000 | +-----------+--------------+ 统计每个省的员工的平均工资 MariaDB [s18]> select city,avg(salary) from emp group by city having avg(salary) > 8000; +-----------+--------------+ | city | avg(salary) | +-----------+--------------+ | 北京 | 10000.000000 | | 山东省 | 9000.000000 | +-----------+--------------+ 查询平均工资大于8000的省份
查询条件顺序
where
group by
having ---> 对平均分组后的内容进行再过滤
order by
limit
MariaDB [s18]> select count(*) from emp; +----------+ | count(*) | +----------+ | 9 | +----------+ 查询公司所有员工的人数 MariaDB [s18]> select avg(salary) from emp; +-------------+ | avg(salary) | +-------------+ | 8666.666667 | +-------------+ 求整个公司的平均薪水 MariaDB [s18]> select * from emp order by salary; +----+-----------+--------+------+-----------+-----------+----------+ | id | name | gender | age | dep | city | salary | +----+-----------+--------+------+-----------+-----------+----------+ | 5 | 张三 | male | 24 | 教学部 | 河北省 | 6000.00 | | 7 | 王五 | male | 38 | 教学部 | 河北省 | 7000.00 | | 1 | tom | male | 24 | 教学部 | 河北省 | 8000.00 | | 2 | jerry | male | 34 | 保安部 | 山东省 | 8000.00 | | 4 | 景丽阳 | female | 22 | 教学部 | 北京 | 9000.00 | | 8 | 赵六 | male | 28 | 保安部 | 河北省 | 9000.00 | | 9 | 猪七 | female | 24 | 保洁部 | 北京 | 9000.00 | | 3 | peter | male | 28 | 保安部 | 山东省 | 10000.00 | | 6 | 李四 | male | 32 | 保安部 | 北京 | 12000.00 | +----+-----------+--------+------+-----------+-----------+----------+ MariaDB [s18]> select * from emp limit 2,4; +----+-----------+--------+------+-----------+-----------+----------+ | id | name | gender | age | dep | city | salary | +----+-----------+--------+------+-----------+-----------+----------+ | 3 | peter | male | 28 | 保安部 | 山东省 | 10000.00 | | 4 | 景丽阳 | female | 22 | 教学部 | 北京 | 9000.00 | | 5 | 张三 | male | 24 | 教学部 | 河北省 | 6000.00 | | 6 | 李四 | male | 32 | 保安部 | 北京 | 12000.00 | +----+-----------+--------+------+-----------+-----------+----------+ 从第2条下面开始取，往下取4条 MariaDB [s18]> select dep,group_concat(name) from emp group by dep; +-----------+-----------------------------+ | dep | group_concat(name) | +-----------+-----------------------------+ | 保安部 | jerry,peter,李四,赵六 | | 保洁部 | 猪七 | | 教学部 | tom,景丽阳,张三,王五 | +-----------+-----------------------------+ 查看每个组的员工姓名
多表查询
MariaDB [s18]> create table emp( -> id int primary key auto_increment, -> name varchar(20), -> salary double(7,2), -> dep_id int); Query OK, 0 rows affected (0.00 sec) MariaDB [s18]> insert emp (name,salary,dep_id) values ('张三',8000,2), -> ('李四',12000,1), -> ('王五',5000,2), -> ('赵六',8000,3), -> ('猪七',9000,1), -> ('周八',7000,4), -> ('蔡九',7000,2); Query OK, 7 rows affected (0.01 sec) MariaDB [s18]> create table dep( -> id int primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> insert into dep (name) values ('教学部'), -> ('销售部'), -> ('人事部'); Query OK, 3 rows affected (0.01 sec) MariaDB [s18]> select * from emp; +----+--------+----------+--------+ | id | name | salary | dep_id | +----+--------+----------+--------+ | 1 | 张三 | 8000.00 | 2 | | 2 | 李四 | 12000.00 | 1 | | 3 | 王五 | 5000.00 | 2 | | 4 | 赵六 | 8000.00 | 3 | | 5 | 猪七 | 9000.00 | 1 | | 6 | 周八 | 7000.00 | 4 | | 7 | 蔡九 | 7000.00 | 2 | +----+--------+----------+--------+ MariaDB [s18]> select * from dep; +----+-----------+ | id | name | +----+-----------+ | 1 | 教学部 | | 2 | 销售部 | | 3 | 人事部 | +----+-----------+
MariaDB [s18]> select * from emp,dep where 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 | 销售部 | +----+--------+----------+--------+----+-----------+ MariaDB [s18]> select dep.name,emp.name from emp,dep where emp.dep_id=dep.id and emp.name='张三'; +-----------+--------+ | name | name | +-----------+--------+ | 销售部 | 张三 | +-----------+--------+
查询张三所在部门的名称
（此种方法并不是以后经常使用的方法,有专门的语法 inner join--内连接查询）
MariaDB [s18]> 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 | 销售部 | +----+--------+----------+--------+----+-----------+ MariaDB [s18]> select * from emp inner join dep on emp.dep_id=dep.id where emp.name='张三'; +----+--------+---------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +----+--------+---------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | +----+--------+---------+--------+----+-----------+
左外连接查询left join（以左边的表为准）
MariaDB [s18]> select * from emp left 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 | 教学部 | | 6 | 周八 | 7000.00 | 4 | NULL | NULL | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | +----+--------+----------+--------+------+-----------+ 比起inner join,多了 | 6 | 周八 | 7000.00 | 4 | NULL | NULL |
右外连接查询right join（以右边的表为准）
MariaDB [s18]> select * from emp right 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 | 销售部 | +------+--------+----------+--------+----+-----------+
完整性约束
1.唯一约束--unique
唯一约束可以有多个但索引列的值必须唯一，索引列的值允许有空值
MariaDB [s18]> create table t1 (name varchar(20) unique); MariaDB [s18]> insert t1 values ('tom'); Query OK, 1 row affected (0.00 sec) MariaDB [s18]> insert t1 values ('tom'); ERROR 1062 (23000): Duplicate entry 'tom' for key 'name' 创建唯一约束后就不能插入相同的内容了
MariaDB [s18]> alter table t1 drop index name; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [s18]> insert t1 values ('tom'); Query OK, 1 row affected (0.01 sec) MariaDB [s18]> insert t1 values ('tom'); Query OK, 1 row affected (0.01 sec) 删除唯一索引后就可以插入重复数据了
MariaDB [s18]> alter table t1 add age int; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [s18]> alter table t1 add constraint UK_t1_age unique (age); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
2.自增约束
MySQL每张表只能有1个自动增长字段，这个自动增长字段通常作为主键，也可以用作
非主键使用，但是将自动增长字段当作非主键使用时必须为其添加唯一索引，否则系统报错
MariaDB [s18]> create table t2 ( -> 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 改为以下： MariaDB [s18]> create table t2 ( -> id int not null, -> name varchar(20), -> age int unique auto_increment); Query OK, 0 rows affected (0.01 sec)
3.主键约束
非空且唯一
a.一张表不一定有主键
b.一张表最多只能有一个主键
c.表中如果没有设置主键，默认设置not null的字段为主键，此外，表中如果有多个not null的字段为主键，则
按顺序将第一个设置为not null的字段设为主键
d.主键一定是非空且唯一，但非空且唯一的字段不一定是主键
e.主键类型不一定必须是整型
MariaDB [s18]> create table t3(id int not null unique,name varchar(20)); Query OK, 0 rows affected (0.01 sec) MariaDB [s18]> desc t3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
添加主键
alter table table_name add primary key(字段名称,...)
删除主键
alter table table_name drop primary key;
4.外键约束
表与表的关系是两张表的关系
一对多
关联字段要设在在数据多的那张表
主表：没有关联字段
子表：有关联字段
多对多
只能通过第三张表来建立关系
一对一
关联字段可以设在任何一张表上
将关联字段设置为unique
复合主键
主键含有一个以上的字段
如果一列不能唯一区分一张表里的记录，可以用多个组合起来达到区分表记录的唯一性
MariaDB [s18]> create table sc ( -> studentid int, -> courseid int, -> score int, -> primary key (studentid,courseid) -> ); 修改 alter table table_name add primary key (字段1,字段2...);