20210315mysql

1.创建用户、授权、取消授权

#创建用户，
#'用户名'@'可以使用该用户名的电脑IP'，%表示在任意电脑上都可以登录
#identified by  密码
create user 'a'@'127.0.0.1'identified by '123456';
create user 'b'@'%'identified by '123456';

#授权某个用户，可以访问那个数据库、表，有什么权限（select、update、insert）。privileges是用户拥有全部权限（除了grant）
grant select on mysql.user to 'a'@'127.0.0.1';
grant all privileges on mysql.* to 'b'@'%';

#取消权限
revoke select from mysql.user to 'a'@'127.0.0.1';
REVOKE all privileges on mysql.* to 'b'@'%';

2.数据库操作（文件夹）

#创建数据库(文件夹)
create database db2 default charset utf8;

#查看数据库
show databases;

#删除数据库
drop database db2;

3.数据表操作（文件）

#查看数据库db2里的表（文件夹里的文件）
use db2;
show tables;

#创建表
#innodb 引擎支持事务
create table t1(
id int not null auto_increment,
name char(10))
engine=innodb 
default charset=utf8;

#查看表内容
select * from t1;

# 查看表结构
show create table t1;

#插入数据
insert into t1(id,name) values(1,'张三');

#清空表数据,再插入数据时，自增字段值原最大值+步幅。
delete from t3;

#清空表数据,再插入数据时，自增字段值从起始值开始。
truncate table t3;

#删除表
drop table t3;

4.create table

create table class (
cid varchar(10) primary key,
caption varchar(10));

create table student(
sid varchar(10) primary key,
sname VARCHAR(10),
gender enum('男','女'),
class_id VARCHAR(10),
CONSTRAINT fk_stu_cla FOREIGN KEY (class_id)  references class(cid));

create table teacher(
tid varchar(10) primary key,
tname VARCHAR(10));

create table course(
cid VARCHAR(10) primary key,
cname varchar(10),
teacher_id VARCHAR(10),
constraint fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(tid)
);

create table score(
sid VARCHAR(10) primary key,
student_id VARCHAR(10),
course_id VARCHAR(10),
cnumber VARCHAR(10),
unique uq_student_course (student_id,course_id),
constraint fk_score_student FOREIGN key (student_id) REFERENCES student(sid),
constraint fk_score_course foreign key (course_id) references course(cid)
);

5.limit

#从位置0开始，查找前两条
select * from city where District like 'Z%' limit 0,2;
select * from city where District like 'Z%' limit 2 offset 0;
#取最后两条
select * from city where District like 'Z%' order by id desc limit 2 offset 0;

6.聚合条件二次筛选having
where 后不能跟聚合函数

select CountryCode,COUNT(CountryCode) c from city GROUP BY CountryCode HAVING c>10;

7.多表连接查询

#统计每个国家使用语言的个数
select c.name,count(c.name) from city c,countrylanguage l where c.CountryCode=l.CountryCode GROUP BY c.name;

#内连接两种方式：A、B两张表连接，结果为A\B表的交集
select count(*) from city c,countrylanguage l where c.CountryCode=l.CountryCode ;#30670
select count(*) from city c inner join countrylanguage l on  c.CountryCode=l.CountryCode ;#30670

#左连接：A、B两张表连接，A全部显示，B显示符合条件的内容，不符合显示NULL
select count(*) from city c left join countrylanguage l on c.CountryCode=l.CountryCode ; #30670

#右连接：A、B两张表连接，B全部显示，A显示符合条件的内容，不符合显
select count(*) from city c right join countrylanguage l on c.CountryCode=l.CountryCode ;#30671

#全连接union, 两个表返回列一致，去重
(select count(*) from city c left join countrylanguage l on c.CountryCode=l.CountryCode) 
union
(select count(*) from city c right join countrylanguage l on c.CountryCode=l.CountryCode);

#全连接union ALL, 两个表返回列一致，不去重
(select count(*) from city c left join countrylanguage l on c.CountryCode=l.CountryCode) 
union all
(select count(*) from city c right join countrylanguage l on c.CountryCode=l.CountryCode);

#多表连接
select * from A 
left join B ON 条件1
left join C on 条件2;

select c1.name,c2.GovernmentForm,c3.Percentage from city c1 
left join country c2 on c1.CountryCode=c2.`Code`
left join countrylanguage c3 on c1.CountryCode=c3.CountryCode;

8.备份、导入数据

#备份表结构、数据
C:\Users\hp>mysqldump -u root -p world > a.sql
Enter password: ********

#备份表结构
C:\Users\hp>mysqldump -u root -p -d  world > b.sql
Enter password: ********

9.临时表

SELECT * FROM (select gender,count(gender) from student GROUP BY gender) as B;

10.操作结果集

-- union：合并，去重
-- union all:合并。不去重