DDL:对库和表继续操作 create drop alter
DML:对表里的数据操作 insert delete update
create table if not exists class ( class_id int primary key, class_name varchar(20) not null unique, class_addr varchar(20) not null )engine innodb default charset utf8; create table if not exists course ( course_id int primary key, course_name varchar(20) not null )engine innodb default charset utf8; create table if not exists student ( student_id int primary key, student_name varchar(20) not null, gender varchar(10) not null, birth date default '2000-01-01', class_id int not null, course_id int not null,
//外键约束, constraint fk_stuclass foreign key(class_id) references class(class_id) on delete no action on update cascade, //on delete no action 父表主键删除记录时,不会影响子表匹配的记录
//on update cascade 父表主键更新时,子表也会同步更新 constraint fk_strcourse foreign key(course_id) references course(course_id) on delete cascade on update cascade )engine innodb default charset utf8;
//插入数据
insert into course values(1,'C'),(2,'C++'),(3,'Go');
insert into class values(1,'一班','一楼'),(2,'二班','二楼'),(3,'三班','三班');
insert into student values(1,'张三','男','1999-01-01',1,3),
(2,'李四','男','1999-01-02',3,1),
(3,'百里猪','女','2002-02-07',1,2);
通过上列语句 创建好表和插入数据后进行查询
select * from student; 查询表中所有记录
select student_name,gender from student; 搜索指定字段的记录
where 添加条件 运算符 = != > < >= <= 等
select * from student where student_id=3; 查询student_id字段为3的记录, 也可以 select 某个字段
select * from student where student_id between 2 and 3; 查询 student_id 2~3的记录, 如果是 not between 2 and 3; 表示非2~3的记录
update class set class_addr='三楼' where class_id=3; 更新数据
like % 和 _ ,%表示匹配任意多个字符, _ 表示匹配一个字符
select * from course where course_name like '%+'; 匹配以 + 字符结尾的任意字段 如果是 +% 表示匹配 +开头的任意字段
select * from course where course_name like 'G_'; 匹配 G 开头且只有两个字符的任意字段
多条件查询 and(与) or(或) not (非)
select * from student where student_id=1 and gender='男'; 查询满足这两个条件的记录
select * from student where student_id=1 or gender='女'; 满足其中一个条件的记录
in
select * from student where student_id in(1,3); 查询in指定的数据范围的记录
select * from student where student_id is null; is查询空值
select distinct class_id from student; 去重复查询, 去掉class_id重复的值只显示一个, 只能修饰一个字段用作统计
分页 limit
select * from student limit 2; 查询表中前两条记录
select * from student limit 1,3; 查询从第二条开始直到第三条的记录
合并结果集, 查询其实就是把表里的数映射到临时的结果集里, 两个互不影响
select 'a',100; 查询结果集里的东西
select 'b',200; 这两条现在是不能输出到同一结果集里
select 'a',100 union 这样就可以把两个结果输出到同一结果集了, 需要注意查询的必须是同一字段才能合并
select 'b',200;
select 'a',100 union 但像这样的重复记录只会显示一个 select 'a',100 union all 这样就不会省略重复
select 'a',100; select 'a',100;
结果集插入到表里, 优先查询数据,并把输出到结果集里的数据插入到指定表中, 也可以把整张表的输出结果集都插入到另一张表里(等于复制)
create table if not exists test ( id int primary key, name varchar(10) )engine innodb default charset utf8; insert into test select 100,'a' union select 101,'b' UNION select 102,'c'; _______________________________________________ create table if not exists test2 ( id int primary key, name varchar(10) )engine innodb default charset utf8; insert into test2 select *from test;
create table test3(select * from test2); //创建表并把查询的结果集表 赋值到新表里
desc test3
create table test4(select name from test3); 当然也可以只查询一部分并赋值
desc test4
需要注意的是,这样复制的表都是没有主键的,需要自行创建 还有一种 select * into newtable from oldtable 这种mysql不支持,sql server支持
取别名
select student_name as 姓名,gender as 性别 from student; 在查询 结果集里帮字段取别名,不会影响源表
排序 select * from student order by class_id; 默认是升序, select * from student order by class_id desc; 这样是降序
create table if not exists salary ( grade varchar(20) primary key, number int )engine innodb default charset utf8; create table if not exists department ( department_id int primary key, department_name varchar(20), salary_grade varchar(20), constraint fk_salary foreign key(salary_grade) references salary(grade) on delete set null on update cascade )engine innodb default charset utf8; create table if not exists employee ( employee_id int primary key auto_increment, employee_name varchar(20), enter_date date default '2000-01-01', department_id int, constraint fk_department foreign key(department_id) references department(department_id) on delete cascade on update cascade )engine innodb default charset utf8; insert into salary SELECT 'S',10000 union ALL SELECT 'A',8000 union ALL SELECT 'B',6000 union ALL SELECT 'C',3000; insert into department select 1,'开发部','S' union all select 2,'测试部','A' union all select 3,'人事部','B' union all select 4,'业务部','C'; insert into employee(employee_name,department_id) 自增和带默认值的字段都可以不输入, 因为日期占了位置,所以需要置顶字段否则无法空隔, 要么就连日期一起输入 select '张三',1 union all select '李四',2 union all select '王五',2 union all select '赵六',4 union all select '钱七',1 union all select '孙八',3 union all select '杨九',2 union all select '陆十',3 union all select '百里猪',1;
交叉连接, 这种查询会出现笛卡尔积再去筛选,效率低
SELECT department.department_name,employee.employee_name
from department,employee
where department.department_id = employee.department_id;
内连接 这种查询会先筛选满足条件再继续两张表的连接, 不会出现笛卡尔积, 提高了效率
SELECT department.department_name,employee.employee_name
from department inner join employee
on department.department_id = employee.department_id;
外连接
左外连接: 基于左表显示出 左表查询字段的所有记录 右表只显示和左表匹配的 , 没有匹配的用null代替
insert into department values(5,'财务部','C'); 插入一个财务部,
SELECT department.department_name,employee.employee_name
from department left join employee
on department.department_id = employee.department_id;
财务部也会通过department_name字段列出, 但没有employee表没有匹配的记录, 所以会用null代替, 内连接会因为没有匹配的记录而不列出财务部
右外连接: 和左表相反,关键字 left替换成right
全外连接: full join具有左右两种连接的效果 但 MySQL不支持 但可以 用union把左右外连接连接查询
SELECT department.department_name,employee.employee_name from department left join employee on department.department_id = employee.department_id union SELECT department.department_name,employee.employee_name from department right join employee on department.department_id = employee.department_id;
select department.department_name,employee.employee_name,salary.number from department inner join employee on department.department_id = employee.department_id inner join salary on department.salary_grade = salary.grade; select 表1.字段1 , 表2.字段2 , 表3,字段3 from 表1 inner join 表2 on 表1.主键 = 表2.关联主键的外键 inner join 表3 on 表2.主键=表3.关联主键的外键 多表查询
函数
select count(*) from tablename 对整张表的有效记录进行计数
select count(*) from tablename where 字段=值 计数符合条件的记录
select count(字段1,字段2) from tablename; 对指定字段计数
max(字段) 计算字段的最大值
min(字段) 计算字段的最小值
avg(字段) 计算字段的平均值
sum(字段) 对字段求和
以上是聚合函数
now() 日期函数
date_format(date,format) 日期转字符串
select date_format(now(),'%Y-%m-%d-%H:%i:%s'); 显示时间戳
upper(str)/icase(str) 转成大写
lower()/lcase() 转成小写
mid(str,start,lenth)/substring(str,start,lenth) 对字符串提取其中一段
round(float,精度); 不写精度时默认四舍五入取整, 可以设置精度
pow(4,2) 4的平方, pow(4,0.5)/pow(4,1/2) 4的开方
分组
exampleselect * from table group by table.字段; 显示以字段分好组的记录
select 字段 from table group by table.字段; 只显示指定字段且分好组的记录
一张表很多客户,有些客户有很多订单 , 查询订单总额小于10k的客户:
select customer,sum(price) from orders group by customer having sum(pirce)<10000;
这句语句意思是, 对customer字段分组, 条件是分组完后每组的pirce总和小于10k的记录, 只显示满足条件的记录中的 customer和price字段的总和
having 和 where 一样都是条件关键字, 但where只能用在分组之前, having只能用在分组之后
