mysql_lesson

DDL(数据库定义语言): create drop alter
针对于数据库或表的结构
DCL(数据库控制语言):grant revoke commit rollback 针对于数据库权限操作、事务的操作
DML(数据库操纵语言):insert delete update select
针对于增删改查的操作
1.MySQL的安装与卸载
2.启动MySQL net start mysql
关闭MySQL net stop mysql
3.mysql -uroot -p123
mysql -h192.168.1.XXX -uroot -p123
4.show databases
5.创建数据库create database wpj1111;
6.使用数据库use wpj1111;
7.建表
create table emp(
Id int,
Name varchar(20),
Age int,
Sex char,
Salary float
);
8.查看表结构
9.插入一条数据
insert into emp(id,name,age,sex,salary) values(2,'cheng',9,'m',9999);
insert into emp values(2,'cheng',9,'m',9999);
10.查询表里的数据
select * from emp;
11.修改表里数据
update emp set salary = 666 where name='cheng';
12.删除记录
delete from emp; 清空表里的数据
delete from emp where id=1;
13.解决中文编码问题
Status查看数据库状态
a.找到D:\Program Files\MySQL\MySQL Server 5.1
下my.ini,找到default-character-set=utf8
stop mysql net start mysql
c.set names gbk;
d.对于改编码之前已经创建好的数据库或者表
alter database wpj1111 default character set utf8;
alter table emp convert to character set utf8;
改好之后exit -> net stop mysql -> net start mysql -> mysql -uroot -p123
14.三大范式
15.为表设置主键
alter table emp add constraint EMP_ID primary key (id); 主键：一条记录的唯一标识
不能为空不能重复
16.主键自增长
auto_increment identity sequence
改字段
alter table emp modify id int auto_increment
17.查询
a.查询年龄从3岁到9岁的所有员工的信息
select * from emp where age>=3 and age<=9;
select * from emp where age between 3 and 9;
b.查询id=5或者id=8的员工的信息
select * from emp where id =5 or id =8;
select * from emp where id in (5,8,10,12);
select * from emp where id not in (5,8,10,12);
c.查询姓"陈"，并且姓名是三个字符的员工信息
模糊查询like "_"匹配一个字符
查询姓名中带有"丽"字的所有员工的信息
select * from emp where name like '%丽%';
搜索功能
d.查询一张表前3条记录
select * from emp limit m,n;
m代表起始记录的索引号第m+1条记录，索引是m n 代表查询的记录条数
查询从第4条到第6条记录
分页
18.聚合函数
sum() 求一列的和累加
count() 求总数数数
avg() 求平均值
max() 求最大值
min() 求最小值
select count(*) from emp;
select sum(salary) 总工资from emp;
select sum(salary) as 总工资from emp;
select min(salary) as 最低工资from emp;
select max(salary) as 最高工资from emp;
最高工资与最低工资相差多少
select max(salary)-min(salary) from emp;
所用员工平均工资
select avg(salary) 平均工资from emp;
求出最高工资的员工的信息
嵌套查询主查询子查询
select * from emp where salary=(select max(salary) from emp);
求出低于平均工资的员工信息
select * from emp where salary < (select avg(salary) from emp);
19.求出所有男员工的工资总和
select sum(salary) from emp where sex='男';
用一条sql语句分别求出男员工和女员工的工资总和分组(几种情况) group
select sum(salary) from emp group by sex;
select sum(salary),sex from emp group by sex;
20.求出总工资最高的那一组的信息
a.由简到繁
b.把查询结果作为查询条件
select * from (select sum(salary) 总工资,sex 性别from emp group by sex ;
第一种做法：(嵌套)
select * from (select sum(salary) 总工资,sex 性别from emp group by sex) t where 总工资=
(select max(总工资) from (select sum(salary) 总工资,sex 性别from emp group by sex) t);
第二种做法：(排序)
select sum(salary) 总工资,sex 性别from emp group by sex order by 总工资desc; (默认升序asc)
select sum(salary) 总工资,sex 性别from emp group by sex order by 总工资desc limit 1;
Select sum(salary) 总工资,sex 性别from emp group by sex order by 总工资desc;
create table emp(id int,name varchar(20),date date,email char,idDep int);
alter table emp add constraint EMP_ID primary key (id); alter table emp add constraint FK_DID foreign key (idDep) references dep(idDep);
Altet table emp add constraint FK_DID foreign key (idDep) references dep(idDep);
Insert into emp values(0,'刘超',1989-03-06,'49374@',1);
Insert into emp values(0,'刘超',1989-03-06,'49374@',1);
create table emp(id int,name varchar(20),date date,email varchar(40),idDep int);
create table emp(idDep int,deparment varchar(40)); alter table emp modify id int auto_increment;。