MySQL常用语句

sql语句
新建数据库 create database if not exists 数据库名
删除数据库 drop database if exists 数据库名

1. 展示所有的数据库 show databases;
2. 切换数据库 use 数据库名;
3. 展示该数据库下所有的表 show tables;
4. 查询语句 `select * from 表名;

DML(数据操作语言)

• 用于操作数据库对象中所包含的数据
• 包括
  • INSERT(添加数据语句)
  • UPDATE(更新数据语句)
  • DELETE(删除数据语句)

1.添加数据(insert)

INSERT INTO 表名[(字段1,字段2,...)]
VALUES('值1','值2',...);

• 字段或值之间用英文逗号隔开
• 字段可省略 但添加的值务必与表结构数据列顺序相对应 且数量一致
• 可同时插入多条数据,values后用英文逗号隔开

2.修改数据(update)

• UPDATE命令

UPDATE 表名
SET column_name=value [,column_name2=value,...][where condition]

• column_name为要更改的数据列
• value为修改后的数据,可以为变量、具体值、表达式或者嵌套SELECT 结果
• condition为筛选条件,如不指定则修改该表的所有列数据

3.1删除数据(DELETE)

• DELETE命令

DELETE FROM 表名 [where condition];

• condition为筛选条件,如不指定则修改该表的所有列数据

3.2删除数据(TRUNCATE)

• TRUNCATE命令
  • 用于完全清空表数据,但表结构、索引、约束等不变

     TRUNCATE [TABLE] table_name;
  

• 区别于DELETE命令
  • 相同
    • 都能删除数据、不删除表结构,但TRUNCATE速度更快
  • 不同
    • 使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器
    • 使用TRUNCATE TABLE不会对事务有影响

4.合并查询(union 、 union all)

union特点: 相同数据不重复展示
union all特点: 展示所有符合条件数据

select * from 表名 where 条件
union
select * from 表名 where 条件;

5.分页查询(limit)

limit 起始下标,获取数据条数
select * from 表名 limit 0,2;
select * from 表名 limit 2,2;

总条数
select count(字段名) from person;

总页数
总页数=总条数 % 每页大小 == 0 ? (总条数 / 每页大小) : (总条数/每页大小+1)

6.聚合函数

1.求总和(sum)
select sum(字段名) from 表名;

2.求平均值(avg)
select avg(字段名) from 表名;

3.求总计数(count)
select count(字段名) from 表名;

4.求最大值(max)
select max(字段名) from 表名;

5.求最小值(min)
select min(字段名) from 表名;

字符串函数

-- 拼接字符串
SELECT concat('he','ll','o');
-- 字符串的替换
select name,replace(mail,'com','*') from student;
-- 转大写
select upper(name) from student;
-- 转小写
select lower(name) from student;
-- 字符串截取
select substring(mail,1,3) from student;
select substring(mail,1,locate('@',mail)-1) from student;

-- 获取某个字符在字符串中的位置
select locate('@',mail) from student;
-- 获取当前时间
select now();

7.链表查询

1.内连接(inner join)

-- inner join 内连接
select * from student inner join score on student.sid=score.sid;
-- 等价于
select * from student,score where student.sid=score.sid;

2.左连接(left join)

-- 左外连接 左表全部展示 
select * from student left join score on student.sid=score.sid;

3.右连接(right join)

-- 右外连接 右表全部展示 左表展示匹配项
select * from score right join subject on score.subjectNo=subject.subjectNo;

-- 自连接
select * from city where name='河南';
select * from city where parentid=(select id from city where name='河南' );
select id from city where parentid=(select id from city where name='河南' );

笔记

-- 创建数据库
create database if not exists MySchool;

-- 删除数据库 
drop database if exists MySchool;

use myschool;

-- case when then else end
select (case name when  '高启强' then '高' else name end) from student;
select (case name when 1=2 then '错误' when 1=1 then '正确' end) as flag from student;


select * from subject;

alter database myschool character set utf8;

-- 查询需要的字段
select subject.subjectNo,subject.subjectname from `subject`;

-- 使用as 修改查询结果的字段名
select subject.subjectNo as '学科编号' from subject;

-- 使用as 给表起别名 多用于多表联查
select * from `subject` as t1;

-- 使用 distinct 去重 
select DISTINCT subjectname from subject;

select distinct name as '姓名' from student as t1;

-- 使用 order by 排序  where 只能跟在表名后面 
select * from student order by grade asc,id;
--降序
select * from student order by grade desc,id desc;

-- group by 分组
select * from student group by name;

-- having 跟在group by后面
select name from student group by name having count(name)>1;

-- 综合使用
select * from student where sex='男' group by name order by id;

-- AND并且 OR或者 
select * from student where sex='男' and id<1020;
select * from student where sex='男' or id<1020;

-- 模糊查询  %匹配所有 _匹配单个
select * from student where name like '%张%';

-- 使用in 查询多条已知数据 
select * from student where sid in (1,2,3);

-- 两表联查
select t1.name,t2.studentResult,t2.subjectno
from student as t1,result as t2 where t1.grade=t2.sid;

-- 三表联查
select t1.name '姓名',t2.studentresult as '分数',t3.subjectname as '科目' from
student as t1,
score as t2,
subject as t3
where t1.sid=t2.sid and t2.subjectno=t3.subjectno;

select  * from student;

-- 使用in 查询多条已知数据 
select * from student where sid in (1,2,3);

-- limit 分页查询
select * from student limit 2;
select * from student limit 2,4;

事务

-- 事物
-- mysql是自动提交事务的 oracle不会自动提交
set autocommit=0; -- 关闭自动提交事务 1打开 0关闭

start transaction; -- 手动开启事务
update student set name='王五' where id=1;
commit; -- 提交
rollback; -- 回滚
set autocommit=1; 

select (case name when  '高启强' then '高' else name end) from student;

select * from student;

select (case name when '高企盛' then'高启盛' when '高企兰' then '高启兰' else name end) as name from student; 
select (case name when 1=2 then '错误' when 1=1 then '正确' end) as flag from student;

select 
t2.subjectNo,
t2.subjectname, 
avg(t1.score),
count(0) AS '考试人数',
sum(case when t1.score between 60 and 70 then 1 else 0 end)*1.0/count(0) '及格',
sum(case when t1.score between 70 and 80 then 1 else 0 end)*1.0/count(0) '中等',
sum(case when t1.score between 80 and 90 then 1 else 0 end)*1.0/count(0) '优良',
sum(case when t1.score between 90 and 100 then 1 else 0 end)*1.0/count(0) '优秀'
from score t1,subject t2
where t2.subjectNo=t1.subjectNo
group by t2.subjectNo

-- 事物
-- mysql是自动提交事务的 oracle不会自动提交
set autocommit=0; -- 关闭自动提交事务 1打开 0关闭

start transaction; -- 手动开启事务
update student set name='李四' where id=1;
commit; -- 提交
rollback; -- 回滚
select * from student;
set autocommit=1; 

lock table student read;
select * from student;

unlock table;
update student set name='安欣' where id=1;

lock table student write;
select * from student;
update student set name='安欣1' where id=1;
unlock tables;

隔离性问题的解决

1.隔离命令

lock table student read;
select * from student;

unlock table;
update student set name='安欣' where id=1;

lock table student write;
select * from student;
update student set name='安欣1' where id=1;
unlock table;

分析 视图 索引

SELECT * FROM `student`
select * from subject
insert into student
values
(9,'张三',23,'男','2001-01-01','洛阳','300',null,null);

-- 视图
select * from student_view

-- 索引
explain select * from student where match(address) against('北大');

-- explain分析
explain select * from student  where  name like '欣%';

函数 变量

-- 通过语法创建 函数
create PROCEDURE test()
begin 
select count(*) from student;
end;

-- 使用函数
call test();

-- 修改结束符 delimiter
delimiter $$
create procedure test03()
begin 
select count(*) from student;
end $$
delimiter ;
-- 删除
drop procedure if exists test03;

-- 声明变量
set @a:=12;
select @a;
set @name:='张三';
select @name;
delete from student where name=@name;
select * from student;

-- 第二种赋值方式
select count(*) into @a from student;
select @a;

-- 在存储过程中声明局部变量
delimiter $$
create procedure test02()
begin
declare username varchar(20) default '高启强';
declare password int default 123;
select * from student where name=username;
end $$
delimiter ;

call test02();