目录
Mysql的优点
Mysql相对于文件系统，优点如下：
-
mysql的结构相对于文件系统更方便管理；
-
mysql支持各种编程语言；
-
mysql比文件系统支持更大规模的数据；
-
mysql更具备扩展性；
-
mysql数据能更快检索；
-
mysql支持分布式。
SQL分类
-
DDL：数据库定义语句，用于数据库、表、列的管理，create、alater、drop
-
DML：数据库表中数据的CUD，表数据的增删改，insert、update、delete
-
DQL：表数据的查询:select
-
DCL：权限控制
-
TCL：事务控制
-
CCL:指针控制、指针管理
用户管理（DDL）
-
查看用户
-
select 列名，列名...from mysql.user;
-
-
创建用户
-
create user '用户名'@'IP地址' idenified by '密码';
-
-
修改密码
-
alter user '用户名'@'IP地址' idenified by '密码';
-
-
删除用户
-
drop user '用户名'@'IP地址';
-
-- 用户管理
-- 查看用户
-- select 列名,列名,... from mysql.user;
select host, user, authentication_string from mysql.user;
-- 创建用户
-- create user '用户名'@'ip地址' IDENTIFIED by '密码';
create user 'zs'@'localhost' IDENTIFIED by '123';
-- 修改密码
alter user 'zs'@'localhost' IDENTIFIED by '456';
权限管理（DDL）
-
用户授权
-
grant 权限码 on 数据库名.表名 to '用户名'@'IP地址';
-
grant all on * . * to 'zs'@'localhost';
-
-
查看用户权限
-
show grants for '用户名'@'IP地址';
-
-
取消用户权限
-
revoke 权限码 on 数据库名.表名 to '用户名'@'IP地址';
-
-
刷新权限
-
flush privileges;
-
-- 权限管理
-- 用户授权
-- grant 权限码 on 数据库名.表名 to 用户名@主机名
grant all on *.* to 'zs'@'localhost';
-- 查看用户权限
-- show grants for 用户名@主机名;
show grants for 'root'@'%';
-- 取消用户权限
-- REVOKE 权限码 ON 库名.表名 FROM 用户名@主机名;
revoke DELETE on *.* from 'zs'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
数据库操作（DDL）
-
创建数据库
-
create database 数据库名称； --直接创建
-
create database if not exists 数据库名称； --如果不存在则创建
-
create database if not exists 数据库名称 character set 编码('UTF8')；
-
-
查看当前所有数据库
-
show database;
-
-
修改数据字符集
-
alter database 数据库名 default character set 字符集；
-
-
删除数据库
-
drop database 数据库名；
-
-
使用数据库（一般用于命令行下使用）
-
use 数据库名；
-
-- 创建数据库
-- create database 数据库名称; --直接创建
-- create database if not EXISTS 数据库名称; --如果不存在则创建
-- create database if not EXISTS 数据库名称 character set 编码; --创建并指定编码
create database if not EXISTS db1;
create database if not EXISTS db2 CHARACTER set 'UTF8';
-- 查看当前所有数据库
show databases;
-- 查看数据库定义语句
SHOW CREATE DATABASE db1;
-- 修改数据库字符集
-- ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
alter DATABASE db2 DEFAULT CHARACTER set 'utf8';
-- 删除数据库
drop DATABASE db2;
数据库表的操作
-
创建表
create table [if not exists] 表名( 字段名 字段类型[primany Key |unique|not null|auto_increment|binary|defualt|comment], ......... )[engine=innodb|defualt charset=utf8(character set 'UTF8')];
-- 快速创建结构一样的表 create table 新表名 like 旧表名；
-
查看表
-
-
查看当前数据库下有哪些表 show tables;
-
查看表的结构 desc 表名；
-
查看建表语句 show create table 表名；
-
-
-
删除表
-
drop table 表名；
-
-
修改表
-- 1. 添加列 alter table 表名 add 列名 类型； -- 2. 修改列类型 alter table 表名 modify 列名 新的类型； -- 3. 修改列名 alter table 表名 change 旧列名 新列名 类型； -- 4. 删除列 alter table 表名 drop 列名； -- 5. 修改表名 rename table 表名 to 新表名； -- 6. 修改字符集 alter table 表名 character set 字符集；
-- 创建数据库
create database db1 CHARACTER set 'UTF8';
-- 使用数据库
use db1;
-- 创建用户表
--create table 表名(
-- 字段名 字段类型(长度),
...
--)
--engine=INNODB 代表数据库引擎，不同引擎有不同的效果，默认是innodb，innodb是一个支持事务的数据库引擎
create table if not EXISTS t_user(
name varchar(255),
sex varchar(255),
age int(3)
) engine=INNODB default charset 'gbk';
-- 快速创建一张表结构一样的表
--CREATE TABLE 新表名 LIKE 旧表名;
create table t_user2 like t_user1;
-- 查看表
--1.查看当前数据库下有哪些表
show tables;
--2.查看表的结构
desc t_user2;
--3.查看建表语句
show create table t_user1;
-- 删除表
drop table t_user;
-- 修改表
--1.添加列
--ALTER TABLE 表名 ADD 列名 类型
alter table t_user add address varchar(255);
--2.修改列类型
--ALTER TABLE 表名 MODIFY 列名 新的类型
alter table t_user MODIFY sex char(2);
--3.修改列名
--ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
alter table t_user change name username varchar(255);
--4.删除列
--ALTER TABLE 表名 DROP 列名;
alter table t_user drop address;
--5.修改表名
--RENAME TABLE 表名 TO 新表名;
rename table t_user to t_user1;
--6.修改字符集
--ALTER TABLE 表名 character set 字符集;
alter table t_user1 character set 'UTF8';
mysql数据类型
-
整数
-
int(常用)
-
bigint(常用)
-
-
浮点数
-
float
-
double(常用)
-
-
定点小数
-
decimal
-
-
日期时间
-
datetime(常用)
-
data(常用)
-
-
字符
-
char:定长字符串
-
varchar：变长字符串(常用)
-
longtext:超大文本
-
-
枚举
-
set
create table t_test(
d1 int,
d2 bigint,
d3 float(4,3),
d4 double(10,4),
d5 datetime,
d6 date,
d7 char(3),
d8 varchar(255),
d9 LONGTEXT
)ENGINE=INNODB character set 'utf8'
表数据的CRUD
-
新增
insert into 表名 (列名，列名...) values(值，值....), [ (值，值....).... ]
-
修改
update 表名 set 列名=值；
-
删除
delete form 表名 ;
-
查询
select (列名，列名...) for 表名； select * form 表名； select 列名 a form 表名； --这一列命名为a
SQL约束
-
非空约束 not null
-
唯一性约束 unique ()
-
默认值约束default
-
主键约束 primary key(保证数据的唯一性) ( )
-
检查约束 check 检查数据是否符合要求()
-
自动增长auto_increment 该列的值自动增长，每次加1
约束的其他添加方式
create table if not exists t_test(
id int auto_increment,
username varchar(255) not null,
sex char(2),
age int default 0,
constraint a primary key(id),
unique(username),
check(age>=0 and age<=150)
)engine=innodb character set 'utf8';
desc t_test;
创建表后 添加/修改约束
-
添加
-
方式一
alter table t_test modify age int check(age>=0 and age<=150)not null default 0;
-
方式二
alter table t_test add constraint a unique(age);
-
删除
alter table t_test drop constraint t_test_chk_1;
查看表中有几个约束
select * form information_schema.table_constraints where table_schema='数据库名' and table_name='表名';
SQL查询
-
简单查询
-
查询指定列
-
查询所有
-
别名
-
运算符
-
算数运算符
+、-、*、/ 、 %
-
比较运算符
!= <>
between 在两值之间
not between 不在两值之间
in 在集合中
not in 不在集合中
like 模糊匹配
姓王：where name like '王%'
包含王：where name like '%王%'
以王结尾：where name like '%王'
is null : 为空
is not null : 不为空
-
逻辑运算符
-- 别名
select a.id '学生编号' from t_test2 a
select 666 '6';
-- 运算符
-- 算术运算符
select 7%3;
-- 比较运算符
select 1!=3;
-- 查询18~25岁的
select * from t_test2 where age >= 18 and age <= 25;
select * from t_test2 WHERE age not BETWEEN 18 and 25;
-- 查询名字叫：张三,李四,王五的人
select * from t_test2 where username = '张三' or username = '李四' or username = '王五';
select * from t_test2 where username not in ('张三','李四','王五');
-- like 查询10~19岁的人
select * from t_test2 where age >=10 and age <=19;
select * from t_test2 where age like '1%';
-- 查询所有姓陈的人
select * from t_test2 where username like '陈%';
-- 查询名字中包含张的人
select * from t_test2 where username like '%张%';
-- 查询名字以五结尾的人
select * from t_test2 where username like '%五';
-- is null is not null
select * from t_test2 where sex is not null;
聚合函数
-
count:计数
-
max:最大值
-
min:最小值
-
sum:求和
-
avg:平均数
select 1 xor 1,0 xor 0,1 xor 0,1 xor null,1 xor 1 xor 1;
-- Max：最大值
--找出表中最大年龄是多少
select max(age) from t_test2;
-- min：最小值
--找出表中最小年龄是多少
select min(age) from t_test2;
-- sum：和
--求所有人的年龄之和
select sum(age) from t_test2;
-- avg：平均值
--求所有人的年龄平均值
select avg(age) from t_test2;
-- count：计数
--求男性有多少人
select count(id) from t_test2 where sex = '男';
排序 order by
order by 列名 ； （默认从小到大）
order by 列名 desc; 从大到小
order by 列名 asc; 从小到大
order by在where之后使用
-- 排序
--按年龄从小到大排序（默认就是小到大）
select * from t_test2 order by age asc;
--按年龄从大到小排序
select * from t_test2 order by age desc;
--按年龄从大到小排序所有的男生
select * from t_test2 where sex ='男' order by age desc;
分页 limit
limit 开始索引，每次查多少条记录
limit 0,2 从0开始索引，一页2行数据
limit 2 ==> limit 0,2(默认开始索引为0)
去重 distinct
select distinct(列名) from 表名；
-- 去重
select distinct(sex) from t_test2;
分组 group by
group by 列名
group by 列名，使用了分组函数之后，聚合函数就会作用在每个分组上。
where/having
having：表示分组后过滤条件，同where类似，但having只能和group by一起使用，不能单独使用。having在group by 后使用。
where和having的区别
where是分组前过滤，having是分组后过滤。
union/union all
union/union all
两者都是用来连接多个结果，区别是
union:会去重
union all：不会去重
-- 分组函数 group by 列名
select * from t_user;
select distinct(dept) from t_user;
select sex from t_user GROUP BY sex;
--1.统计各部门有多少人
-- 聚合函数和分组函数一起使用时，聚合函数交作用于每个组
select dept,count(id) from t_user GROUP BY dept;
--2.统计各部门的平均薪资
select dept, avg(money) from t_user group by dept;
--3.求各地区薪资最高是多少
select address,max(money) from t_user group by address;
--4.求各地区男女人数是多少？
-- select address, sex, count(id) from t_user group by address, sex;
--union & union all
select address, sex, count(id) from t_user where sex = '男' group by address
union all
select address, sex, count(id) from t_user where sex = '女' group by address;
select name,address from t_user where dept = '测试部'
union
select name,address from t_user where dept = '总经办';
关联查询
表关联关系
-
一对一 任意一方
-
一对多/多对一 关联关系放在多的一方
-
多对多 使用第三张表关联
查询跨多表
-
左连接 left join on
select * from t_student s left join t_class c on s.class_id = c.id;
-
右连接 right join on
select * from t_student s right join t_class c on s.class_id = c.id;
-
内连接 inner join on
select * from t_student s inner join t_class c on s.class_id = c.id;
-
左外连接
select * from t_student s left join t_class c on s.class_id = c.id where c.id is null;
-
右外连接
select * from t_student s right join t_class c on s.class_id = c.id where s.id is null;
-
全连接
select * from t_student s INNER JOIN t_class c on s.class_id = c.id union select * from t_student s left join t_class c on s.class_id = c.id where c.id is null union select * from t_student s right join t_class c on s.class_id = c.id where s.id is null;
-
全外连接
select * from t_student s left join t_class c on s.class_id = c.id where c.id is null union select * from t_student s right join t_class c on s.class_id = c.id where s.id is null;
-
笛卡尔积：
select * from t_student s, t_class c where s.class_id = c.id;
-- 21、查询不同课程但成绩相同的学号，课程号，成绩
-- 自连接 拿自己跟自己关联
select DISTINCT s1.student_id, s1.course_id, s1.num, s2.num from score s1, score s2
where s1.num = s2.num and s1.course_id != s2.course_id;
42万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
