数据库基础创建数据以及数据库和表的增删改查语句：
# 查询所有数据库
show databases ;
# 查询当前数据库
select database();
# 创建数据库
create database if not exists itcast; -- 如果数据库itcast不存在时创建数据库itcast
# 使用数据库
use test;
# 删除数据库
drop database if exists itcast ; -- 如果数据库itcast存在时删除数据库
#创建一张用户表
create table tb_user(
id int comment 'id唯一标识',
usrname varchar(20) comment '用户名',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char comment '性别'
)comment '用户表';
# 添加约束条件
create table tb_user(
id int primary key comment 'id唯一标识', -- 添加主键约束
usrname varchar(20) not null unique comment'用户名', -- 添加非空约束和唯一约束
name varchar(10) not null comment'姓名', -- 添加非空约束
age int comment '年龄',
gender char default '男' comment '性别' -- 添加默认值约束，默认值为男
)comment '用户表';
#创建一个homework数据库
create database homework;
use homework;
# 学生表
create table student(
id int primary key auto_increment comment 'id唯一标识',
name varchar(10) not null comment '姓名',
gender char default '男' comment '性别',
age tinyint unsigned comment '年龄'
)comment '学生表';
#课程表
create table course(
id int unsigned primary key comment '课程唯一标识',
classname varchar(10) not null unique comment '课程名称',
credit double(4,1) comment '学分',
eml varchar(20) unique comment '邮箱'
);
# 员工表
create table employee(
id int primary key auto_increment comment '员工唯一标识',
name varchar(10) not null comment '员工姓名',
department varchar(10) default '未分配' comment '部门名称',
date date comment '入职时间',
salary tinyint comment '薪资',
telephone varchar(11) comment '手机号',
#创建时间
create_time timestamp default current_timestamp comment '创建时间'
);
#查看 表
show tables;
#查看表结构
desc student;
#查看表创建语句
show create table student;
use test;
create table tb_user (
id int primary key comment 'ID,唯一标识',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
age int comment '年龄',
gender char(1) default '男' comment '性别'
) comment '用户表';
#
create table emp(
id int unsigned primary key auto_increment comment 'ID,主键',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1:男, 2:女',
phone char(11) not null unique comment '手机号',
job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
salary int unsigned comment '薪资',
image varchar(300) comment '头像',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';
#添加列 添加字段
alter table emp add qq varchar(10) comment 'QQ号码';
#改列 修改字段
alter table emp change qq qq_num varchar(11) comment '新QQ号码';
#删除列 删除表名
alter table emp drop qq_num;
#重命名 表名
rename table tb_user to user;
#向指定字段增加数据
insert into emp (username, password, name, gender, phone, create_time, update_time)
values ('admin', '123456', '管理员1', 1, '13888888888', now(), now());
#全部字段添加数据
insert into emp
values (2,'user2',123457,'用户2',1,'13888888889',1,5000,'user.png','2023-03-01',now(),now());
#批量添加数据（指定字段）
insert into emp (id,username, password, name, gender, phone, create_time, update_time)
values (3,'admin2', '123458', '管理员2', 1, '13888888887', now(), now()),
(4,'admin3', '123459', '管理员3', 1, '13888888886', now(), now());
#批量添加数据（全部字段）
insert into emp
values (5,'user3',123455,'用户3',1,'13888888885',1,5000,'user.png','2023-03-01',now(),now()),
(6,'user4',123454,'用户4',1,'13888888884',1,5000,'user.png','2023-03-01',now(),now());
#修改数据
update emp set name ='张三',update_time = now() where id = 1;
#全部修改数据
update emp set entry_date = '2010-01-01',update_time = now();
#删除某一行数据
delete from emp where id = 1;
#删除全部数据
delete from emp;
#删除某一个数据
update emp set job = null where id = 2;
#
create table emp(
id int unsigned primary key auto_increment comment 'ID,主键',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1:男, 2:女',
phone char(11) not null unique comment '手机号',
job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
salary int unsigned comment '薪资',
image varchar(300) comment '头像',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO emp(id, username, password, name, gender, phone, job, salary, image, entry_date, create_time, update_time)
VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'1.jpg','2000-01-01','2023-10-27 16:35:33','2023-10-27 16:35:35'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'2.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'3.jpg','2008-05-01','2023-10-27 16:35:33','2023-10-27 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'4.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'5.jpg','2012-12-05','2023-10-27 16:35:33','2023-10-27 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'6.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'7.jpg','2005-08-01','2023-10-27 16:35:33','2023-10-27 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'8.jpg','2014-11-09','2023-10-27 16:35:33','2023-10-27 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'9.jpg','2011-03-11','2023-10-27 16:35:33','2023-10-27 16:35:51'),
(10,'lichong','123456','林冲',1,'13309090010',1,5000,'10.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'11.jpg','2007-02-01','2023-10-27 16:35:33','2023-10-27 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'12.jpg','2008-08-18','2023-10-27 16:35:33','2023-10-27 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'13.jpg','2012-11-01','2023-10-27 16:35:33','2023-10-27 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'14.jpg','2002-08-01','2023-10-27 16:35:33','2023-10-27 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'15.jpg','2011-05-01','2023-10-27 16:35:33','2023-10-27 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'16.jpg','2010-01-01','2023-10-27 16:35:33','2023-10-27 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'17.jpg','2015-03-21','2023-10-27 16:35:33','2023-10-27 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'18.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'19.jpg','2008-01-01','2023-10-27 16:35:33','2023-10-27 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'20.jpg','2018-01-01','2023-10-27 16:35:33','2023-10-27 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'21.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'22.jpg','2016-01-01','2023-10-27 16:35:33','2023-10-27 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'23.jpg','2012-01-01','2023-10-27 16:35:33','2023-10-27 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'24.jpg','2006-01-01','2023-10-27 16:35:33','2023-10-27 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'25.jpg','2002-01-01','2023-10-27 16:35:33','2023-10-27 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'26.jpg','2011-01-01','2023-10-27 16:35:33','2023-10-27 16:36:25'),
(27,'lijun','123456','李俊',1,'13309090027',5,6600,'27.jpg','2004-01-01','2023-10-27 16:35:33','2023-10-27 16:36:27'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'28.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:36:29'),
(29,'songqing','123456','宋清',1,'13309090029',5,5100,'29.jpg','2020-01-01','2023-10-27 16:35:33','2023-10-27 16:36:31'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'30.jpg','2020-03-01','2023-10-27 16:35:33','2023-10-27 16:36:31');
#查询某些数据
select name,entry_date from emp ;
#查询全部数据
select * from emp;
#给查询的字段起别名
select name as 姓名,entry_date as 入职时间 from emp;
select name as '姓 名',entry_date as '入职时间' from emp;
select name as "姓 名",entry_date as "入职时间" from emp;
#给查询的数据去重
select distinct emp.job from emp;
#案例1：查询 姓名 为 '杨逍' 的员工
select name from emp where name='杨逍';
#案例2：查询 薪资小于等于 5000 的员工信息
select * from emp where salary <= 5000;
#案例3：查询 没有分配职位 的员工信息
select * from emp where job is null;
#案例4：查询 有职位 的员工信息
select * from emp where job is not null;
# 案例5：查询 密码不等于 '123456' 的员工信息
select * from emp where password != '123456';
select * from emp where password <> '123456';
#案例6：查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01';
select * from emp where entry_date >= '2000-01-01' and entry_date <= '2010-01-01';
# 案例7：查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01' and gender = 2;
select * from emp where entry_date >= '2000-01-01' and entry_date <= '2010-01-01' and gender = 2;
#案例8：查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from emp where job in (2,3,4);
#案例9：查询 姓名 为两个字的员工信息
select * from emp where name like '__';
#案例10：查询 姓名 前两个字是 '张' 的员工信息
select * from emp where name like ('宋%');
select * from emp where name like '宋_';
#案例11：*查询 姓名中包含* '二' 的员工信息
select * from emp where name like '%二%';
#查询员工总人数
select count( *) from emp;
select count(id) from emp;
select count(name) from emp;
select count(job) from emp; -- 忽略空值
select count(1) from emp;
#求平均薪资
select avg(emp.salary) from emp;
#求发放薪资总和
select sum(emp.salary) from emp;
#求最高薪资
select max(salary) from emp;
#求最低薪资
select min(emp.salary) from emp;
#统计性别男女的人数
select gender,count(*) from emp group by gender;
#统计2015-01-01前入职人数大于2的职位
select job,count(*) from emp where entry_date <= '2015-01-01' group by job having count(*) >=2;
#根据入职时间对员工进行升序排序。
select * from emp order by entry_date asc;
#跟据入职时间进行升序排序，入职使劲按相同的根据更新时间进行降序排序。
select * from emp order by entry_date asc ,update_time desc;
#
select *
from emp
limit 0,5;
#
select *
from emp
limit 5,5;
#1.
insert into emp
values (31,'yangxiao','123456','杨逍一',1,'13309090031',2,11000,'31.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:01');
# 2.
insert into emp
values (32,'yangxiaoer','123456','杨逍二',1,'13309090032',2,11000,'32.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:01'),
(33,'yangxiaosan','123456','杨逍三',1,'13309090033',2,11000,'33.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:01');
#4.
delete from emp where id in (30,31);
# 5.
update emp
set emp.entry_date = '2018-09-10',password = '1234567' where id in (6,7,9,12);
#1.
select name as 姓名,entry_date as 入职时间 from emp;
# 2.查询返回所有字段
select * from emp;
select id,username,password,name,gender,phone,job,salary,image,entry_date,create_time,update_time from emp;
# 3.
select name as 姓名,entry_date as 入职时间 from emp where gender=2 and job is not null;
#4.
select distinct job from emp;
# 5.
select * from emp where name = '童威' and entry_date between '2000-01-01' and '2010-01-01';
# 6.
select * from emp where salary <= 5000 ;
# 7.
select * from emp where job is null;
# 8.
select * from emp where job is not null;
# 9.
select * from emp where password != '123456' or job is null ;
# 10.
select * from emp where entry_date between '2000-01-01' and '2010-01-01';
# 11.
select * from emp where entry_date between '2000-01-01' and '2010-01-01' and gender = 2 ;
# 12.
select * from emp where job in (2,3,4);
select * from emp where job = 2 or job = 3 or job = 4;
# 13.
select * from emp where name like '__' and gender = 1 and salary > 5000;
# 14.
select * from emp where name like '%小%' and entry_date > '2010-01-01';
# 15.
select * from emp where gender = 1 or entry_date > '2010-01-01';
# 16.
select * from emp where job is not null and name like '__';
# 17.
select * from emp where gender = 1 and job = 2;
# 18.
select * from emp where salary < 8000 or entry_date > '2010-10-09';
# 19.
select gender,count(*) from emp group by gender;
# 22.
select job,count(*) from emp where entry_date <= '2015-01-01'group by job having count(*) >=2;
