MySQL的常见SQL语句
DDL 数据库定义语言
查看当前数据库
SELECT DATABASE();
退出数据库
QUIT[;]
显示数据库列表
SHOW DATABASES;
SHOW SCHEMAS;
显示数据库版本
SELECT VERSION();
当前登录数据库的用户
SELECT USER();
显示当前时间
SELECT NOW();
数据库帮助
HELP CREATE DATABASE
? CREATE
\H CREATE
取消执行SQL
SELECT USER()\c
创建数据库
CREATE DATABASE test1;
CREATE SCHEMA test1;
转义反引号关键字
CREATE DATABASE `database`;
创建检查数据库是否存在
CREATE DATABASE IF NOT EXISTS test1;
查看警告
SHOW WARNINGS;
创建数据库指定编码
CREATE DATABASE test IF NOT EXISTS DEFAULTER CHARACTER SET = 'UTF8';
查看创建的编码方式
SHOW CREATE DATABASE test;
查看表
SHOW TABLES;
SHOW FULL TABLES FROM mysql;
查看键表信息
SHOW CREATE TABLE imooc_user;
查看表结构
DESC test;
DESCRIBE test;
SHOW COLUMNS FROM test;
打开数据库
USE test;
ALTER
改变编码方式
ALTER DATABASE test DEFAULTER CHARACTER SET = 'UTF8';
添加字段
ALTER TABLE user1 ADD username VARCHAR(20);
添加字段在某个字段的后面
ALTER TABLE user
ADD email VARCHAR(50) NOT NULL UNIQUE KEY AFTER username;
添加字段在首位
ALTER TABLE user1
ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;
删除字段
ALTER TABLE user1
DROP test;
添加删除组合使用
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP email;
存在基础上修改字段添加默认值SET
ALTER TABLE user2
ALTER email SET DEFAULT 'imooc@qq.com';
存在基础上修改字段删除默认值
ALTER TABLE user2
ALTER age DROP DEFAULT;
修改字段名称
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL;
修改字段的类型 NOT NULL要加上
ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL ;
修改字段的类型 并放在首位
ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;
去掉AUTO_INCREMENT
ALTER TABLE user5
MODIFY id INT UNSIGNED;
添加唯一性索引
ALTER TABLE user6
ADD UNIQUE KEY(username);
删除唯一性索引
ALTER TABLE user6
DROP INDEX username;
添加唯一性索引并指定索引名称
ALTER TABLE user6 ADD UNIQUE INDEX uni_email(email);
添加主键
ALTER TABLE user4
ADD PRIMARY KEY(id);
修改表名称
ALTER TABLE user6
RENAME TO user666;
ALTER TABLE user666
RENAME AS user6;
RENAME TABLE user6 TO user666;
删除外键
ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;
添加外键
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId)
REFERENCES news_cate(id); ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId)
REFERENCES news_cate(id);
DROP
删除数据库
DROP DATABASE IF EXISTS test;
删除表
DROP TABLE IF EXISTS imooc_user;
注释
#
--
插入数据
INSERT INTO imooc_user()VALUES(); INTO可以省略
INSERT INTO imooc_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(1,'king','king','382771946@qq.com',24,'230112197809871234',18635579617,888888.68,0,'北京','男');
function
CONCAT()
SELECT CONCAT('*',a,'*'),CONCAT('*',b,'*') FROM test_str;
NOW()
INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME());
当前日期时间
SELECT NOW()
当前时间
SELECT CURRENT_TIME();
当前时间
SELECT CURTIME();
CURRENT_DATE()
INSERT INTO test_date(a) VALUES(CURRENT_DATE());
当前日期
CURRENT_DATE()
CURRENT_TIMEStamP
INSERT test_timestamp(a) VALUES(CURRENT_TIMEStamP);
当前日期和时间
CURRENT_TIMEStamP
进一取整
SELECT CEIL(1.2);
去掉小数
SELECT FLOOR(2.9);
四舍五入 取两位小数
SELECT ROUND(3.567,2);
取小数3位部分
SELECT TRUNCATE(3.456789,3);
取余数
SELECT MOD(5,2);
取绝对值
SELECT ABS(-12);
幂运算
SELECT POWER(2,3);
得到PI
SELECT PI();
随机数0-1之间
SELECT RAND();
定给参数会变成一个标识
比如SELECT RAND(1);
取符号 0为0 -n(取n)>0为-1 +n>0为+1
SELECT SIGN(-23);
e的几次方
SELECT EXP(2);
得到字符长度以占位符来区分
SELECT CHAR_LENGTH('你好啊');
真正字符串长度
SELECT LENGTH('abc');
合并字符串 如果有null最终结果是null
SELECT CONCAT('a','b','c',null);
以指定的分隔符拼接字符串，分隔符是null就是null，其他地方是null没影响
CONCAT_WS('-','A','B','C')
CONCAT_WS('-','A','B','C',null)
CONCAT_WS(null,'A','B','C')
大小写转换 UPPER('hello king') UCASE('hello imooc') LOWER('HELLO ADMIN') LCASE('HELLO EVERYBODY');
SELECT UPPER('hello king'),UCASE('hello imooc'),LOWER('HELLO ADMIN'),LCASE('HELLO EVVERYBODY');
字符串反转函数
SELECT REVERSE('abc');
得到前2个或者后2个字符
SELECT LEFT('hello',2),RIGHT('hello',2);
用字符串填充到指定长度
SELECT LPAD('abc',10,'?');
SELECT RPAD('abc',10,'!');
去掉空格
SELECT CONCAT('*',TRIM(' abc '),'*'),CONCAT('*',LTRIM(' abc '),'*'),CONCAT('*',RTRIM(' abc '),'*');
字符串替换
SELECT REPLACE('hello king','king','queen');
重复字符数
SELECT REPEAT('hello',3);
截取字符串
SELECT SUBSTRING('abcdef',1,3);
比较字符串 相等为0 第一个字符串大于第二个字符串返回是正1 第一个字符串小于第二个字符串返回是-1
SELECT STRCMP('a','a');
返回当前日期
SELECT CURDATE(),CURRENT_DATE();
返回当前时间
SELECT CURTIME(),CURRENT_TIME();
返回当前的日期时间
SELECT NOW(),CURRENT_TIMEStamP(),SYSDATE();
返回日期中的月和月份名称
SELECT MONTH('2017-02-19');
SELECT MONTH(CURDATE()),MONTHNAME(CURDATE());
返回星期几的名称
SELECT DAYNAME(NOW());
返回一周内的第几天
SELECT DAYOFWEEK(NOW());
返回第几个星期了
SELECT WEEK(NOW());
返回年份 月份 号 小时 分钟 秒
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
计算两个日期相差的天数
SELECT DATEDIFF('2017-03-06','2017-03-02');
其他常用函数
获得版本
SELECT VERSION();
当前连接数
SELECT CONNECTION_ID();
获得当前的数据库名
SELECT DATABASE(),SCHEMA();
当前登录的用户
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
得到上一次auto_increment的值
SELECT LAST_INSERT_ID();
MD5加密
SELECT MD5('king');
密码加密算法
SELECT PASSWORD('root');
查询root密码
SELECT * FROM mysql.user WHERE user='root';
查看表状态
SHOW TABLE STATUS LIKE 'myisam_1';
设置定长格式
CREATE TABLE myisam_2(
a VARCHAR(30),
id INT
)ENGINE=MYISAM ROW_FORMAT=FIXED;
检查表
CHECK TABLE test_innodb;
修复表
REPAIR TABLE test_innodb;
插入数据省略
INSERT user VALUE(1,'king',24,'382771946@qq.com');
INSERT user VALUES(1,'king',24,'382771946@qq.com');
一次插入多条记录
INSERT user VALUES(NULL,'A',DEFAULT,DEFAULT),
(NULL,'b',56,'b@qq.com'),
(NULL,'c',14,'c@qq.com');
INSERT SET形式
INSERT user SET username='d',age=45,email='d@qq.com';
UPDATE SET
UPDATE user SET username='socialpg',age=18 ,email='qq@com' WHERE id=3;
重置AUTO_INCREMENT
ALTER TABLE user AUTO_INCREMENT=1;
彻底删除数据表
TRUNCATE user;
查询指定库中的表
SELECT * FROM imooc.user1;
字段起别名
SELECT id AS '编号',username AS '用户名' , sex AS '性别'FROM user1;
数据库起别名
SELECT id,username FROM user1 AS u;
表名.字段名
SELECT user1.id,user1.username FROM user1;
<=>检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc <=> NULL;
IS NULL
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NULL ;
IS NOT NULL 取反
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NOT NULL ;
BETWEEN AND
SELECT id,username,age,userDesc FROM user1
WHERE age BETWEEN 18 AND 30;
NOT BETWEEN AND
SELECT id,username,age,salary FROM user1
WHERE salary NOT BETWEEN 18 AND 30;
IN指定集合范围
SELECT id,username,age FROM user1 WHERE id IN(1,3,5,7,9,29,45,78);
NOT IN 不在指定集合范围
SELECT id,username,age FROM user1 WHERE id NOT IN(1,3,5,7,9,29,45,78);
BETWEEN AND AND AND
SELECT id,username,age,sex,salary addr FROM user1
WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';
LIKE 模糊查询 字符串 LIKE不区分大小写的
SELECT id,username,age FROM user1 WHERE username LIKE 'king';
LIKE %% 任意一个字符
SELECT id,username,age,sex FROM user1 WHERE username LIKE '%三%';
LIKE ___ 一个字符 限定位数
SELECT id,username,age,sex FROM user1 WHERE username LIKE '___';
GROUP BY hash算法 过滤掉相同的，只保留一条第一次检索出来的数据作为代表，不同的记录成为不同的一个组,多的数据会第一显示出来，这是默认情况下，但可以通过函数显示出这个组中的详细的各个记录的信息，一组查该组的所有记录,会派出一个sex代表出来
SELECT id,username,age,sex FROM user1 GROUP BY sex;
GROUP BY 和 GROUP_CONCAT函数
SELECT GROUP_CONCAT(username),age,sex,addr FROM user1 GROUP BY sex;
COUNT(*) 统计有多少条记录数 就是每条每条记录
SELECT COUNT(*) AS total_users FROM user1;
COUNT(字段)在统计字段，如果字段值为空就不记录出来
SELECT COUNT(userDesc) FROM user1;
CONUT(*)配合GROUP BY 使用的时候 COUNT(*) 统计的是 GROUP BY 分组中的记录数
SELECT sex,GROUP_CONCAT(username) AS usersDetail ,COUNT(*) AS toalUsers FROM user1 GROUP BY sex;
GROUP BY 是配合聚合函数使用的 聚合函数受GROUP BY 影响
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
GROUP BY addr;
WITH ROLLUP 会在最后一条记录加上各个分组合和聚合函数统计的结果相加到一起
SELECT GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1 GROUP BY sex WITH ROLLUP;
POSITION 按照SELECT的顺序字段位置分
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
GROUP BY 1;
Having是二次 是对分组的结果 受分组影响 可以加 聚合函数 select查询的列
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr HAVING COUNT(*);
Having可通过别名
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr HAVING totalUsers
>=3;
ORDER BY DESC 降序 ASC升序
SELECT id,username,age FROM user1 ORDER BY id DESC;
多个ORDER BY,一个ORDER BY 不能满足可通过多个
SELECT id,username,age FROM user1 ORDER BY age ASC,id ASC;
RAND() 返回[0-1) 随机排序
SELECT id,username,age FROM user1 ORDER BY RAND();
LIMIT显示5条记录
SELECT id,username,age,sex FROM user1 LIMIT 5;
LIMIT从指定显示记录
SELECT id,username,age,sex FROM user1 LIMIT 0,5;
LIMIT分页实现 从第4条开始显示3条也就是到6条 包括4本身 LIMIT受ORDER BY影响
SELECT id,username,age,sex FROM user1 LIMIT 3,3;
综合练习
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC
LIMIT 0,1;
笛卡尔积 相等于二层循环 第一个记录数*第二个表记录数
SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;
一对一 内连接 INNER JOIN ON 条件 交集共有
SELECT e.id,e.username,e.age,d.depName FROM emp AS e INNER JOIN dep AS d on e.depId = d.id;
左连接 LEFT OUTER JOIN 显示左表所有记录 以ON为条件 右边符合的显示 不符合的右表以NULL形式显示
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;
右连接 RIGHT OUTER JOIN 显示右表所有记录 以ON为条件 左表符合的显示 不符合的右表以NULL形式显示
SELECT e.id,e.username,e.age,d.depName,d.depDesc FROM emp AS e RIGHT OUTER JOIN dep AS d ON e.depId=d.id;
三表复合多表连查单表关系 缺点性能不好
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p JOIN admin AS a ON p.adminId = a.id
JOIN cate AS c ON p.cateId=c.id WHERE p.price<1000
ORDER BY p.price DESC LIMIT 0,2;
四表
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p JOIN admin AS a ON p.adminId = a.id
JOIN cate AS c ON p.cateId=c.id WHERE p.price<1000
ORDER BY p.price DESC LIMIT 0,2;
级联操作
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;
子查询
IN 在集合中查询
SELECT * FROM emp WHERE id IN (1,2,3,4);
SELECT * FROM emp WHERE depId IN (SELECT id FROM dep);
NOT IN 不在集合中查询
SELECT * FROM emp WHERE depId NOT IN (SELECT id FROM dep);
>= 子查询
SELECT id,username,score FROM stu WHERE score>=
(SELECT score FROM level WHERE id=1);
EXISTS 子查询 true || false
SELECT * FROM emp WHERE EXISTS (SELECT * FROM dep WHERE id=1);
>= any最小值 all最大值
SELECT * FROM stu WHERE score >= ANY(SELECT score FROM level);
= ANY等于任意值 存在ANY中的任意值才返回
SELECT * FROM stu
WHERE score = ANY(SELECT score FROM level);
!= <> ALL不等于任意值 不存在ALL中的任意值都会返回
SELECT * FROM stu
WHERE score != ALL(SELECT score FROM level);
<= any最大值 all最小值
SELECT * FROM stu
WHERE score < ALL(SELECT score FROM level);
CREATE根据select创建表
CREATE TABLE user1(
id TINYINT AUTO_INCREMENT KEY,
username VARCHAR(20)
)SELECT id,username FROM user;
INSERT根据select创建表
INSERT INTO user1(username) SELECT username FROM user;
INSERT不指定字段直接通过select创建表
INSERT user2 SELECT * FROM user1;
INSERT SET 形式通过子查询插入数据
通过like创建一模一样的表 但是没数据
CREATE TABLE user2 LIKE user1;
DISTINCT去掉重复的列select查询
SELECT DISTINCT(username) FROM user2;
联合查询 UNION 合并重复数据 留下不重复针对多个表的，单个表就不用
SELECT * FROM user1
UNION SELECT * FROM user2;
UNION ALL 简单的合并 两个表并在一起
SELECT * FROM user1
UNION ALL SELECT * FROM user2;
自连接 无限极分类 虚拟出两种相同的表，只不过在创建表时的条件不一样 查询所有分类信息，并且得到父类
SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate AS s LEFT JOIN cate AS p ON s.pid=p.id;
自连接 无限极分类 虚拟出两种相同的表，只不过在创建表时的条件不一样 查询所有分类信息及其子分类
SELECT p.id,p.cateName AS pCateName,s.cateName AS sCateName
FROM cate AS s RIGHT JOIN cate AS p ON p.id=s.pid;
自连接 分组 查询所有的分类及其子分类的数目
SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS count
FROM cate AS s
RIGHT JOIN cate AS p
ON p.id=s.pid
GROUP BY p.cateName ORDER BY id ASC;
全连接
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
WHERE Table.id IS NULL OR TableB.id IS NULL
