MySQL数据库

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;



-------------------------------------------------------------------------