|
show databases;
create database db1 character set utf8;
use mydata;
SELECT DATABASE;
show create DATAbase 数据库名；#查看一个数据库的定义信息
修改数据库字符集
alter database db1 character set utf8;
show create database db1
删库
drop database db1；
建表
use db1;
create table db1_1(cid int, cname varchar(10));
创建一个表结构与db1相同的表db2
create table db2 like db1;
查看表结构
desc 表名;
查看表
show tables;
查看创建表的sql语句
show create table category；
删表
drop table if exists test1;
drop table test1;
修改表名
rename table test1 to test2;
表中添加列
alter table test1 add name varchar(20);
修改表中的列的数据类型或长度
alter table test1 modify name char(50);
修改表中的列名
alter table test1 change name address varchar(30);
删除列
alter table test1 drop address;
表中插入数据
create table student(sid int, sname varchar(20), age int)
insert table student(sid,sname,age) values(001, "孙倩", 18),(002, "孙俪", 17),(003, "马超", 19)；
insert table student values(004, "孙悟空", 20);#插入全部字段值
insert table student(sname) values("孙千") #插入指定字段值
更改数据
update student set sname="马冬梅", age=17 where sid=002;
删除数据
delete from student; #删除所有数据（有多少条记录 就执行多少次删除操作）
delete from student where age=18;
truncate table student;#先删除整张表，再重新创建一张一模一样的表
#查询
#执行顺序 from- where- group by-having- select-order by
select 字段 from 表 where 条件 ?ORDER BY 字段名;
#模糊查询
select 字段 from 表 where 字段 like '%精%';#查询含有'精'字的信息?
select 字段 from 表 where 字段 like '精%';
#查询以'精'字开头的信息
select 字段 from 表 where 字段 like '_精%';
#查询第二个字为'精'字的信息
#聚合函数--纵向查询，对某列的值进行计算，聚合函数会忽略控制
#count() sum() max() min() avg()
#!!! group by 的字段必须出现在前面select 的位置
#前面select的位置，除了group by的字段、聚合函数，不能出现其他的字段
#where与having的区别：where进行分组前的过滤，where后面不能写聚合函数；having是分组后的过滤，having后面可以写聚合函数
select 聚合函数(字段名) from 表名;
#指定行数查询limit
select 字段 from 表名 limit offset,length; #offset起始行数，若省略默认为0
#length 返回的行数
SELECT * FROM emp LIMIT 3 , 6; 查询emp表中 从第4条开始,查询6条
#sql约束: 主键primary key 唯一unique 非空not null 外键foreign key 、（默认值 自增）
#创建带主键的表
方式一
REATE TABLE emp2(
-- 设置主键 唯一 非空
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1) not NULL
);
方式二
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1),
-- 指定主键为 eid字段
PRIMARY KEY(eid)
);
#创建表未指定主键
alter table emp2 add primary key(eid);
#删除主键约束
alter table emp2 drop primary key;
#修改/自定义主键自增的起始值
REATE TABLE emp2(
-- 设置主键 唯一 非空
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)auto_increment=100;
#DELETE和TRUNCATE对自增长的影响：
#DELETE只是删除表中所有数据，对自增没影响
#TRUNCATE是将整张表删除掉，然后创建一个新的表自增的主键，重新从1开始
#外键约束
#外键：数据表A中有一列，这一列指向了另外一张表B的主键；
#从表：外键所在的表，被约束的表；外键所指向的表叫主表；
#创建外键
1、建表时添加外键?
#[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
constraint emp_dept_fk foreign key(dep_id) references department(id));
2、已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
3、删除外键
alter table employee drop foreign key emp_dept_fk;
#外键约束事项：
1、从表的外键类型必须与主表的外键类型一致，否则创建失败
2、添加数据时，要先添加主表的数据
3、删除数据时，要先删除从表的数据
#主表从表同时删除数据--级联删除on delete cascade
#建从表时添加级联操作
CREATE TABLE employee(
eid INT PRIMARY KEY auto_increemnt,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dep_fk FOREIGN KEY(dept_id) REFERENCES department(id)?
on DELETE CASCADE);
#多表查询
1、交叉查询---会产生笛卡尔积，基本不用
select 字段 from 表1, 表2;
SELECT * FROM category , products;
2、内连接查询
#隐式内连接
SELECT * FROM products,category WHERE category_id = cid;?
#显示内连接
SELECT * FROM products p INNER JOIN category c WHERE p.category_id = c.cid;
3、外连接
#左外连接
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
#右外连接
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
4、合并查询
4.1、union
#union内部的select语句必须有相同数量的列，列也必须要相似的数据类型，每条select语句中的列的顺序必须相同，列的名称不必相同
SELECT Id,NAME,Amount,Date
FROM customers
LEFT JOIN orders
on customers.Id = orders.Customers_Id
UNION
SELECT Id,NAME,Amount,Date
from customers
RIGHT JOIN orders
on customers.Id = orders.Customers_Id;
4.2、UNION ALL
与union 运算符规则一致，但是查询结合组合在一起包含重复行
5、子查询
5.1、子查询的结果作为条件：where/from/exists型 子查询
select 字段 from where 字段=(select子查询);
5.2、子查询结果作为表
select 查询字段 from (子查询) 表别名 where 条件;
#注意：子查询作为一张表时要起别名，否则无法访问表中的字段
5.3、子查询结果是单列多行
select 查询字段 from 表 where 字段 in(子查询)；
6、sql函数
6.1、数学函数
ABS(X) 返回x的绝对值
FLOOR(X) 返回x的绝对值
ROUND(X)
RAND()返回0～1的随机数
PI() 返回圆周率的值
MOD(N,M) 返回N除以M以后的余数
6.2、字符串函数
CONCAT(str1,str2,...) 字符串拼接
LEFT(str,len) 返回从字符串s开始的n最左字符
TRIM(s) 除掉字串中s的字头或字尾处空格
REPLACE(s,s1,s2)字符串s2替代字符串s重的字符串s1
SUBSTRING(s,n,len) 截取字符串s中第n个位置开始，长度为len的字符串
MID(str,pos,len) 同SUBSTRING
REVERSE(s) 将字符串s的顺序翻转过来
6.3、日期时间函数
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW()
MONTH(date)
YEAR(date)
DAY(date)
6.4、条件判断函数
IF()
CASE WHEN
SELECT CASE WHEN 10>5 THEN 10
ELSE 5
END AS "最大值"
6.5、系统信息函数
VERSION() 数据库的版本号
DATABASES() ?当前数据库名
USER() 当前用户名
7、窗口函数
函数名() over(PARTITION by 要分列的组 order by 要排序的列 rows between 数据范围)
计算的行数范围：
rows between 2 preceding and current row
# 取当前行和前面两行
rows between unbounded preceding and current row# 包括本行和之前所有的行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行
rows between current row and unbounded following# 包括本行和之后所有的行
7.1专有窗口函数
rank()
dense_rank()
row_number()
7.2聚合类窗口函数
sum()
count()
avg()
max()
min()
7.3排序函数
row_number() over(......)
rank() over(......)
dense_rank() over(......)
7.4结果分组
ntile(n) over(......)
#ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
#NTILE不支持ROWS BETWEEN，
7.5偏移函数
lag(...) over(......)
lead(...) over(......)
#Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为 独立的列
8、MySQL索引
8.1、主键索引
建表时添加主键索引
CREATE TABLE test_index(
did INT primary key,
dname VARCHAR(20),
hobby VARCHAR(30)
);
修改表结构 添加主键索引
alter table 表名 add primary key(列名)；
8.2唯一索引---保证数据记录的唯一性
建表时添加唯一索引
CREATE TABLE test_index(
did INT primary key,
dname VARCHAR(20),
hobby VARCHAR(30),
UNIQUE hobby_index (hobby)
);
使用create语句创建: 在已有的表上创建索引
create unique index 索引名 on 表名(列名);
修改表结构添加索引
alter table 表名 add unique 索引名(列名)；
8.3普通索引----普通索引的唯一任务是加快对数据的访问速度
使用create index 语句创建: 在已有的表上创建索引
create index 索引名 on 表名(列名)；
修改表结构添加索引
alter table 表名 add index 索引名(列名)；
8.4删除索引
later table table_name drop index index_name;
9、视图
create view view_name as select语句；
|