SQL语言分类：
DDL 结构 create alter drop
create database dbsname;
create table tblname(colname 数据类型 [属性],colname 数据类型 [属性]);
create user usname identified by password;
alter table tblname1 rename as tblname2;
alter table tblname1 modify colname 数据类型 属性；
alter table tblname1 change oldname newname 数据类型 属性；
alter table tblname1 add colname 数据类型 属性；
alter table tblname1 drop colname;
drop database dbsname;
drop table tblname;
drop user username;
DML 数据 insert update delete
insert into tablename values ();
insert into tablename values (),(),();
insert into tbalename(colname1,colname2) values (1,2),(2,3);
update tablename set colname1=?,coname2=? where 条件;
条件：> < >= <= != <> = and or between and
//删除一行或者多行记录
delete from tablename where 条件；
DCL grant revoke commit rollback
grant select on 库名.表名 to 用户名@'%' with grant option;
revoke select on 库名.表名 from 用户名@'%';
事务操作：（事务只能控制dml语句，ddl语句自动自交事务）
1.set autocommit = 0;
2.start transaction;
3.执行操作 dml
4.commit ,rollback
5.set autocommit = 1;
事务4个特性：ACID
1.原子性
2.一致性
3.隔离性
4.持久性
DQL
约束：
主键约束 primary key 唯一 非空 并且一个表中只能有一个主键
外键约束 foreign key 不唯一 可以为空 ，但是如果不为空，必须参照主表字段值
mysql> create table employee(
-> id int primary key,
-> name char(16),
-> address char(16)
-> )
-> ;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into employee(id,name,address) values (1001,'李嘉琪','河南洛阳');
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee(id,name,address) values (1001,'邵晓凯','河南汝州');
ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'
mysql> insert into employee(name,address) values ('邵晓凯','河南汝州');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into employee(id,name,address) values (1002,'邵晓凯','河南汝州');
Query OK, 1 row affected (0.00 sec)
删除约束：
删除主键约束：
alter table employee drop primary key;
更新表结构添加主键约束
alter table employee add constraint crd primary key (id);
mysql> alter table employee add constraint pk_employee_id primary key (id);
ERROR 1062 (23000): Duplicate entry '1002' for key 'PRIMARY'
mysql> delete from employee where id=1002;
Query OK, 2 rows affected (0.00 sec)
mysql> alter table employee add constraint pk_employee_id primary key (id);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建表的时候添加外键约束
constraint 约束名 约束类型（列名） 参照 主表表名（主表列名）
constraint ky_salary_empId foreign key(empId) references employee(id)
create table salary(
id int primary key auto_increment comment '流水号',
empId int ,
salary double,
constraint ky_salary_empId foreign key(empId) references employee(id)
)
insert into salary(empId,salary) values (1,5000);
insert into salary(empId,salary) values (1001,5000);
insert into salary(salary) values (5000);
删除外键约束
alter table salary drop foreign key ky_salary_empId;
动态添加外键约束
alter table salary add constraint ky_salary_empId foreign key(empId) references employee(id);
mysql> create table salary(
-> id int primary key auto_increment comment '流水号',
-> empId int ,
-> salary double,
-> constraint ky_salary_empId foreign key(empId) references employee(id)
-> );
Query OK, 0 rows affected (0.22 sec)
mysql> insert into salary(empId,salary) values (1,5000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`java1803_03`.`salary`, CONSTRAINT `ky_salary_empId` FOREIGN KEY (`empId`) REFERENCES `employee` (`id`))
mysql> insert into salary(empId,salary) values (1001,5000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into salary(salary) values (5000);
Query OK, 1 row affected (0.00 sec)
mysql> alter table salary drop foreign key ky_salary_empId;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table salary add constraint ky_salary_empId foreign key(empId) references employee(id);
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
DQL select
正式系统中，尽可能避免*的出现，*的效率非常低 *代表一个表中所有的列，需要先查询数据字典，从字典表中找到该表所有的列名，然后再查询
select * from employee;
查询部分列，所有行。
select 列名1，列名2，列名3 from employee;
select id ,name ,address from employee;
select id 员工编号,name 员工姓名,address 联系地址 from employee;
select id as 员工编号,name as 员工姓名,address as 联系地址 from employee;
select distinct address from employee; ##distinct 关键字，查询过程中让重复记录只出现一次
查询部分行，需要添加where限定条件。
select id,name,address from employee where address='河南洛阳';
select id,name,address from employee where address='河南洛阳' or address='河南汝州';
select id,name,address from employee where address in ('河南洛阳','河南汝州');
select id,name,address from employee where address like '%洛阳%'; ## % 和like结合使用，代表0到多个字符
查询表达式
select * from salary;
select id,empId,salary-500 as 纳税工资 from salary;
select version(),1+99;
##查询员工编号为null的记录
select * from salary where empId is null;
##查询员工编号不为null的记录
select * from salary where empId is not null;
##查询员工姓名、工资(对应)
内连接（查询内部能够关联上的数据）
select 列名1，列名2 from a,b,c whree a.列名=b.列名 and a.列名=c.列名
select name,salary from employee,salary where employee.id=salary.empId;
select 列名1，列名2 from a join b on a.**=b.** join c on a.**=c.**;
select name,salary from employee join salary on employee.id=salary.empId;
select name,salary from employee inner join salary on employee.id=salary.empId;
（左外连接，不管左表数据是否能关联上，全部显示）查询所有员工姓名，工资
select name,salary from employee left outer join salary on employee.id=salary.empId;
select name,salary from employee left join salary on employee.id=salary.empId;
（右外连接，显示右边表的所有数据）查询员工姓名，所有人工资（即使不在员工表）
select name,salary from employee right outer join salary on employee.id=salary.empId;
select name,salary from employee right join salary on employee.id=salary.empId;
##如果两个表中有相同字段名，取值时要指定该字段来自哪个表
select employee.id '员工id',name,address,salary.id '工资流水',empId,salary from employee right outer join salary on employee.id=salary.empId;
select e.id '员工id',name,address,s.id '工资流水',empId,salary
from employee e left outer join salary s
on e.id=s.empId;
##查询所有员工员工编号、姓名、工资，并按照工资降序排序
select e.id,e.name,s.salary
from employee e join salary s
on e.id = s.empId
order by s.salary desc;
##asc或者不写，代表升序排序
select e.id,e.name,s.salary
from employee e join salary s
on e.id = s.empId
order by s.salary asc;
##查询所有员工员工编号、姓名、工资，先按照工资降序排序，如果工资相同，再按照员工编号升序排序
select e.id,e.name,s.salary
from employee e join salary s
on e.id = s.empId
order by s.salary desc,e.id asc;
##查询所有员工员工编号、姓名、工资，先按照工资降序排序，如果工资相同，再按照员工编号降序排序
select e.id,e.name,s.salary
from employee e join salary s
on e.id = s.empId
order by s.salary desc ,e.id desc;
##分页 指定开始行号，每页显示多少条数据 limit startRow(开始行号从0开始),pageSize（每页显示数据条数）
select * from salary limit 0,2;
select * from salary limit 2,3;
##查询所有员工员工编号、姓名、工资，工资在6000员以上，先按照工资降序排序，如果工资相同，再按照员工编号降序排序
select e.id,e.name,s.salary
from employee e join salary s
on e.id = s.empId
where s.salary > 6000
order by s.salary desc ,e.id desc;
统计函数
##统计员工表中员工总数 count(*)统计表中数据行数
select count(*) from employee;
##统计工资表中工资总和 sum(列名) 数值总和
select sum(salary) from salary;
##统计表中最高的工资、最低工资、平均工资
select max(salary),min(salary),avg(salary) from salary;
##统计每个城市的员工人数 （统计函数，分组函数）
select count(*),address from employee group by address;
##统计每个城市的员工人数,只显示统计人数大于2的 （统计函数，分组函数）
## where后的限定条件中不能使用统计函数
## 如果需要对统计函数进行限定，需要使用having
select address,count(*) from employee group by address having count(*)>2;
select salary,count(*) from salary group by salary;
##根据工资分组，统计工资对应的人数，并且只显示工资大于6000
select salary,count(*) from salary where salary>6000 group by salary;
select 列名，count()
from 表名a join 表名b
on a.**=b.**
where a.**=? and or
group by 分组列名
having count(*)>2
order by 列名 asc/desc,列名2 asc/desc
limit startRow,pageSize
insert into salary(empId,salary) value (1002,7000),(1003,9000),(1004,7500);
##从一个表中复制数据到另外一个表中
insert into salary(empId,salary) select empId,salary from salary;
##创建一个新表salary_1，让新表和salary表结构一样，数据也一样。
create table salary_1 as select * from salary;
##创建一个新表salary_2，让新表和salary表结构一样，但是不拷贝数据。
create table salary_2 as select * from salary where 1=0;
-------------------------------------------------------------------------
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
