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:合并。不去重