数据库参考
DDL-数据库操作
show databases;查看所有数据库
select database();查询当前处于哪个数据库
create database 数据库名称 ；创建数据库
drop database 数据库名称；删除指定数据库
use 数据库名称 ； 使用数据库
为表添加一列“入库时间（S_time）”Date类型
alter table store add column S_time Date;
为表中Sname字段加上唯一约束
alter table store add constraint unique_Sname unique(Sname);
修改“入库时间”字段数据类型为varchar(20)
alter table store modify column S_time varchar(20);
修改 字段名称 Saddress 为 Saleaddress
alter table store rename column Saddress to Saleaddress;
为字段Sname添加唯一索引，索引名称为uSname
alter table store add constraint uSname unique(Sname);
删除 索引uSname
alter table store drop index uSname;
删除表Store.
drop table store;
DDL-表结构创建
show databases 查看当前数据库有哪些表
create table 表名 创建新的表
第一步->create table 表名(
字段(id) 类型(int) comment ‘标签说明’，
…
…
…
最后-> ) comment ‘注释’；
show tables;展示表名；
desc 表名；查询表有哪些字段结构
show create table 表名；查询指定表
alter table 表名 add 字段名 类型 （加标签comment）添加
alter table 表名 change 旧字段名 新字段名 型字段名 类型 （commnet注释）修改字段名和字段类型
alter table 表名 drop 字段名 ；删除字段
alter table 表名 rename to 新表名；
drop table [if exists] 表名; sho删除表
truncate table 表名； 删除指定表并重新创建表
DML
insert into 表名 （表中建的名称比如id）values(赋值内容)
select * from 表名；查看赋值内容
insert into 表名 values(赋值内容);
update newlist set （修改内容：name=’秋华’，可多条修改内容）（条件where id=1，不加条件即范围为全局）;修改指定内容
delete from newlist （可加条件）;
DQL
create table emp(
id int comment ‘编号’,
workid varchar(10) comment’工号’,
name varchar(10) comment ‘姓名’,
gender char(1) comment ‘性别’,
age tinyint unsigned comment ‘年龄’,
idcard char(18) comment ‘身份证号’,
workaddress varchar(50) comment ‘工作地址’,
entrydate date comment ‘入职时间’
)comment ‘员工表’;
use itqiuhua;
desc emp;
truncate table emp;
insert into emp (id, workid, name, gender, age, idcard, workaddress, entrydate)
values (1,’1′,’柳岩’,’女’,20,’123456789012345678′,’北京’,’2000-01-01′),
(2,’2′,’张无忌’,’男’,18,’123456789012345670′,’北京’,’2005-09-01′),
(3,’3′,’韦一笑’,’男’,38,’123456789712345670′,’上海’,’2005-08-01′),
(4,’4′,’赵敏’,’女’,18,’112345675712384567′,’北京’,’2009-12-01′),
(5,’5′,’小昭’,’女’,16,’123456769012345678′,’海南’,’2007-07-01′),
(6,’6′,’杨道’,’男’,28,’12345678931234567X’,’北京’,’2006-01-01′),
(7,’7′,’范瑶’,’男’,40,’123456789212345670′,’北京’,’2005-05-01′),
(8,’8′,’黛绮丝’,’女’,38,’123456157123645670′,’天津’,’2015-05-01′),
(9,’9′,’范凉凉’,’女’,45,’123156789012345678′,’北京’,’2010-04-01′),
(10,’10’,’陈友谅’,’男’,53,’123456789012345670′,’上海’,’2011-01-01′),
(11,’11’,’张士诚’,’男’,55,’123567897123465670′,’江苏’,’2015-05-01′),
(12,’12’,’常遇春’,’男’,32,’123446757152345670′,’北京’,’2004-02-01′),
(13,’13’,’张三丰’,’男’,88,’123656789012345678′,’江苏’,’2620-11-01′),
(14,’14’,’灭绝’,’女’,65,’123456719012345670′,’西安’,’2019-05-01′),
(15,’15’,’胡青牛’,’男’,70,’12345674971234567X’,’西安’,’2018-04-01′),
(16,’16’,’周芷若’,’女’,18,null,’北京’,’2012-06-01′);
select *from emp;
# 查询姓名年龄
select name,age,entrydate from emp;
# 查询所有字段
select * from emp;
# 查询工作地址并起别名
select workaddress ‘工作地址’ from emp;
# 查询工作地址并起别名不要重复的
select distinct workaddress from emp;
# 条件查询
# 查询年龄在88岁的员工
select name from emp where age=88;
#查询年龄小于20岁的员工
select name from emp where age<20;
select * from emp where age<20;
# 查询没有身份证的员工信息
select * from emp where idcard is null;
# 查询有身份证的员工
select * from emp where idcard is not null;
# 查询年龄不是88岁的员工
select * from emp where age!=88;
# 查询年龄在20包含到30岁之间的员工
select * from emp where age>=20&&age<=30;
select * from emp where age>=20 and age<=30;
select * from emp where age between 20 and 30;
# 查询年龄小于三十岁且性别为女
select * from emp where age<=30&&gender=’女’;
# 查询年龄为20,30,40的员工的信息
select * from emp where age=20 or age=30 or age=40;
select * from emp where age in(20,30,40);
# 查询名字为俩个子的员工信息
select * from emp where name like ‘__’;
# 查询身份证最后一位位X的员工
select * from emp where idcard like ‘%X’;
# 聚合函数
# 求表中的总人数
select count(*) ‘总数’ from emp;
# 统计表中人的平均年龄
select avg(age) ‘年龄’ from emp;
#统计年龄最小的员工
select min(age) from emp;
# 统计年龄最大的员工
select max(age) from emp;
# 求西安地区员工年龄总和
select sum(age) from emp where workaddress=’西安’;
# 分组查询
# 根据性别分组，统计男性员工和女性员工的数量
select gender,count(gender) from emp group by gender;
# 根据性别分组，统计男性和女性员工的平均年龄
select gender,avg(age) from emp group by gender;
#查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress,count(workaddress) from emp where age<45 group by workaddress having count(workaddress)>3;
# 排序查询
# 根据年龄对公司员工进行升序查询
select * from emp order by age asc;
# 根据入职时间进行降序排序
select * from emp order by entrydate desc;
# 根据年龄对公司员工进行升序排序，年龄相同，再按入职时间进行降序排序
select * from emp order by age asc,entrydate desc ;
# 分页查询
# 第一页
select * from emp limit 10;
# 第二页
select * from emp limit 10,10;
# 练习
# 查询年龄为20，21，22，23的员工信息
select * from emp where gender=’女’ and age in(20,21,22,23);
# 查询性别为男年龄在20-40岁姓名为三位的员工信息
select * from emp where age between 20 and 40 and gender=’男’ and name like ‘___’;
# 统计员工年龄小于60岁的男员工和女员工
select gender,count(gender) from emp where age<=60 group by gender;
# 查询年龄小于等于35岁的员工姓名和年龄，并对查询后的年龄进行升序排序，如果年龄相同就按入职时间降序
select name,age from emp where age<=35 order by age ,entrydate desc;
# 查询性别为男，且年龄在20-40岁的前五位的员工信息，对查询结果按年龄进行升序排序，年龄相同就按入场时间升序排列
select * from emp where gender=’男’ and age between 20 and 40 order by age asc,entrydate limit 5;
# 执行顺序,从上到下优先执行
# from
# where
# group by
# having
# select
# order by
# limit
# 例子
# 查询年龄大于15岁的员工的姓名，年龄，并根据年龄进行升序排序
select emps.name n,emps.age a from emp emps where emps.age>15 order by a asc;
DCL
# localhost换成%是任意主机都可登录
create user ‘itcast’@’localhost’ identified by ‘123456’;
# 创建用户itqiuhua用户访问密码是123456任意主机都可访问
create user ‘itqiuhua’@’%’ identified by ‘123456’;
create user ‘ithua’@’%’ identified by ‘1234567’;
# 修改密码
alter user ‘itqiuhua’@’%’ identified with mysql_native_password by ‘1234567’;
# 删除用户
drop user ‘itcast’@’localhost’;
drop user ‘itqiuhua’@’%’;
# 查询权限
show grants for ‘ithua’@’%’;
# 赋予权限
grant all on itqiuhua.* to ‘ithua’@’%’;
#撤销权限
revoke all on itqiuhua.* from ‘ithua’@’%’;
# 字符串函数
# 拼接函数
select concat(‘Hello’,’Mysql’);
#lower字符变成小写
select lower(‘HELLO’);
# upper 变成大写
select upper(‘hello’);
# lpad左填充
select lpad(’01’,5,’-‘);
# rpad右填充
select rpad(’01’,5,’-‘);
#trim去除前面和后面的空格
select trim(‘ hello mysql ‘);
# substring截取字段
select substring(‘hello mysql’,1,5);
# 例子
# 修改工号为五位左填充
update emp set workid=lpad(workid,5,’0′);
select * from emp;
# 数值函数
# ceil向上取整
select ceil(2.45);
#floor向下取整
select floor(1.5);
#rand
select rand();
#mod取模
select mod(6,5);
#round四舍五入
select round(2.456,2);
# 例子
# 通过数据库随机生成一个六位数的验证码
select lpad(substring(rand(),4,6),6,’0′);
# 日期函数
#当前日期
select current_date();
# 当前时间
select current_time();
#
select now();
# 年月日
select year(now());
select month(now());
select day(now());
select time(now());
# data_add
select date_add(now(),interval 3 day );
# datediff
select datediff(now(),’2002-4-8′);
# 例子
# 获取员工入职天数，并按入职天数倒置排序
select name,datediff(now(),entrydate) worktime from emp order by worktime desc;
# 流程函数
# if ifnull
select if(false,’ok’,’error’);
select ifnull(null,’default’);
# case when then else end
select
name,
(case workaddress when ‘北京’ then ‘一线城市’ when ‘上海’ then ‘一线城市’ else ‘二线城市’ end) ‘工作城市’
from emp;
# 例子
create table score(
id int comment ‘学号’,
name varchar(50) comment ‘姓名’,
math int comment ‘数学成绩’,
english int comment ‘英语成绩’,
chinese int comment ‘语文成绩’
) comment ‘成绩表’;
insert into score values (1,’tom’,67,88,95),(2,’rose’,23,60,90),(3,’jack’,56,78,96);
select * from score;
# case when then else end
# 展示各个班级学员成绩->=85优秀->=60是及格否则是不及格
select
id,
name,
(case when math>=85 then “优秀” when math>=60 then ‘及格’ else ‘不及格’ end) ‘数学成绩’,
(case when english>=85 then “优秀” when english>=60 then ‘及格’ else ‘不及格’ end) ‘英语成绩’,
(case when chinese>=85 then “优秀” when chinese>=60 then ‘及格’ else ‘不及格’ end) ‘语文成绩’
from score;
— 约束
— primary key 主键 auto_increment自由增长
— not null 不空 unique 唯一
— check() 判断条件
— default 没有指定值默认为
create table user(
id int primary key auto_increment comment ‘主键’,
name varchar(10) not null unique comment ‘姓名’,
age int check ( age>0 and age <120 ) comment ‘年龄’,
status char(1) default 1 comment ‘状态’,
gender char(1) comment ‘性别’
) comment ‘用户表’;
— 外键约键
create table depart(
id int auto_increment comment ‘ID’ primary key,
name varchar(50) not null comment ‘部门名称’
) comment ‘部门表’;
insert into depart(id, name) VALUES (1,’研发部’),(2,’市场部’),(3,’财务部’),(4,’销售部’),(5,’总经部’);
desc depart;
select * from depart;
alter table emp auto_increment=1;
create table emp(
id int auto_increment comment ‘ID’ primary key,
name varchar(50) not null comment ‘姓名’,
age int comment ‘年龄’,
job varchar(20) comment ‘职位’,
salery int comment ‘薪资’,
entrydate date comment ‘入职时间’,
managerid int comment ‘领导ID’,
dept_id int comment ‘部门ID’
) comment ‘员工表’;
drop table emp;
drop table depart;
select * from emp;
insert into emp(name, age, job,salery,entrydate, managerid, dept_id) values
(‘金庸’,66,’总裁’,20000,’2002-4-8′,null,5),(‘张无忌’,20,’项目经理’,12500,’2003-5-3′,1,1),
(‘杨过’,45,’开发’,8400,’2004-9-6′,2,1),(‘韦一笑’,48,’开发’,11000,’2006-4-4′,2,1),
(‘常遇春’,63,’开发’,12000,’1992-4-6′,3,1),(‘小昭’,36,’程序员奖励师’,5000,’2000-5-8′,2,1);
— 插入外键
alter table emp add constraint dept_id foreign key (dept_id) references depart(id);
— 删除外键
alter table emp drop foreign key dept_id;
— 外键的删除和更新行为
alter table emp add constraint dept_id foreign key (dept_id) references depart(id) on update set null on delete set null ;
alter table emp add constraint dept_id foreign key (dept_id) references depart(id) on update cascade on delete cascade ;
Comments NOTHING