0.mysql语句
DDL(Data Definition Language)数据定义语言。例如:建库、建表
DML(Data Manipulation Language)数据操作语言。例如:对表中的数据进行增删改操作
DQL(Data Query Language)数据查询语言。例如:对数据进行查询
DCL(Data Control Language)数据控制语言。例如:对用户的权限进行设置
1.DDL(Data Definition Language)数据定义语言
数据库的创建与使用
创建数据库
CREATE DATABASE IF NOT EXISTS 数据库;
CREATE DATABASE IF NOT EXISTS test;
查看当前在哪个数据库里
SELECT DATABASE ();
进入库
USE 数据库;
USE test;
查看数据库字符集
SHOW CREATE DATABASE 数据库;
SHOW CREATE DATABASE test;
修改数据库字符集
ALTER DATABASE test DEFAULT CHARACTER SET utf8;
ALTER DATABASE 数据库 DEFAULT CHARACTER SET 字符集;
查看mysql使用的字符集
SHOW VARIABLES LIKE 'character%';
数据表的创建
数据表创建
CREATE TABLE 表名 (
字段名 字段类型 约束条件 说明,
......
);
CREATE TABLE student (
id INT NOT NULL auto_increment,
`name` VARCHAR ( 100 ) NOT NULL COMMENT '姓名',
PRIMARY KEY ( id )
) ENGINE = INNODB CHARSET = utf8;
数据表创建仅复制表结构
CREATE TABLE 新表 LIKE 旧表;
CREATE TABLE stu1 LIKE student;
约束条件
comment:说明解释
not null:不为空
default:默认值
unsigned:无符号(即正数)
auto_increment:自增
zerofill:自动填充
unique key:唯一键
数据表结构查看
查看数据库中的所有表
SHOW TABLES;
查看表结构
DESC 表名;
DESC student;
查看创建表的sql语句
SHOW CREATE TABLE 表名;
SHOW CREATE TABLE student;
数据表结构维护与删除
修改表名
RENAME TABLE 旧表名 TO 新表名;
RENAME TABLE students TO student;
添加列
ALTER TABLE 表名 ADD 字段 类型 COMMENT '描述' AFTER 列名;
ALTER TABLE student ADD age TINYINT COMMENT '年龄' AFTER `name`;
修改列
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型 COMMENT '描述';
ALTER TABLE student CHANGE age age INT COMMENT '年龄';
删除列
ALTER TABLE 表名 DROP 列;
ALTER TABLE student DROP age;
修改字符集
ALTER TABLE 表名 CHARACTER SET 字符编码;
ALTER TABLE student CHARACTER SET gbk;
删除表
DROP TABLE IF EXISTS 表名;
DROP TABLE IF EXISTS test;
2.DML(Data Manipulation Language)数据操作语言
表数据插入
普通的插入表数据
INSERT INTO 表名 ( 字段名 ) VALUES ( 字段值 );
INSERT INTO student ( NAME ) VALUES ( '张三' );
批量插入
INSERT INTO 表名 ( 字段名 ) VALUES ( 值 ),( 值 );
INSERT INTO student ( `name` ) VALUES ( '张三' ),( '李四' );
-- 蠕虫复制(将一张表的数据复制到另一张表中)
INSERT INTO 表名1 ( 字段名 ) SELECT 字段名 FROM 表名2;
INSERT INTO stu1 ( `name` ) SELECT `name` FROM student;
建表复制
CREATE TABLE 新表 AS SELECT 字段名 FROM 已有表;
CREATE TABLE stu2 AS SELECT id,`name` FROM student;
表数据修改删除
更新
UPDATE student SET 字段 = 值 WHERE 条件;
UPDATE student SET `name` = 'tom' WHERE id = 1;
删除
DELETE FROM 表名 WHERE 条件;
DELETE FROM student WHERE id = 1;
-- truncate不会记录删除操作,会把表占用空间恢复到最初,不会删除定义
TRUNCATE TABLE 表名;
-- delete会把删除的操作记录给记录起来,以便数据回退,不会释放空间,不会删除定义
DELETE FROM 表名;
-- drop会删除整张表,释放表占用的空间
DROP TABLE 表名;
删除速度:drop>truncate>delete
3.DQL(Data Query Language)数据查询语言
where条件查询
简单查询
SELECT * FROM 表名;
SELECT * FROM employee;
精确条件查询
SELECT * FROM 表名 WHERE 条件;
SELECT * FROM employee WHERE job = '董事长';
模糊条件查询
SELECT * FROM 表名 WHERE 字段 LIKE 条件;
SELECT * FROM employee WHERE ename LIKE '唐%';
范围查询
SELECT * FROM 表名 WHERE 字段 BETWEEN 区间 AND 区间;
SELECT * FROM employee WHERE sal BETWEEN 5000 AND 10000;
离散查询
SELECT * FROM 表名 WHERE 字段 IN ( 值1, 值2 );
SELECT * FROM employee WHERE ename IN ( '唐僧', '张飞' );
清除重复值
SELECT DISTINCT ( 字段 ) FROM 表名;
SELECT DISTINCT ( job ) FROM employee;
聚合函数查询
SELECT 函数 FROM 表名;
SELECT count(*) FROM employee;
count(*):记录条数
sum():计算总和
max():计算最大值
avg():计算平均值
min():计算最小值
concat():起连接作用
group by分组查询
作用:把行按字段分组
语法:group by 列1, 列2, 列n
使用场景:常用于统计场合,一般和聚合函数连用
SELECT job,count(*) FROM employee GROUP BY job
having条件筛选
作用:对查询的结果进行筛选操作
语法:having 条件 或者 having 聚合函数条件
使用场景:一般跟在group by之后
SELECT job, count(*) FROM employee GROUP BY job HAVING count(*) >= 2
order by排序
作用:对查询的结果进行排序操作
语法:order by 字段1,字段2
使用场景:一般用在查询结果的排序
SELECT * FROM employee ORDER BY sal DESC;
limit限制
作用:对查询结果起到限制条数的作用
语法:limit m,n; m:代表起始条数值,不写默认为0;n:取出条数
使用场景:数据量过多时,起到限制作用
SELECT * FROM employee LIMIT 1,3;
exists子查询
exists型子查询后面是一个受限的select查询语句
exists子查询,如果exists后的内层查询能查出数据,则返回true表示存在,为空则返回false则不存在
SELECT * FROM 表1 WHERE EXISTS ( SELECT 1 FROM 表2 WHERE 条件 );
SELECT
*
FROM
dept a
WHERE
EXISTS ( SELECT 1 FROM employee b WHERE a.deptnu = b.deptnu );
左连接、右连接
左连接称之为左外连接,右连接称之为右外连接,这两个都是外连接
左连接:left join 表名 on 条件;左表的记录会全部表示出来,而右表只会显示符合搜索条件的记录,右表记录不足的地方均为null。
右连接:right join 表名 on 条件;与左连接相反,左表只会显示符合搜索条件的记录,而右表的记录将会全部表示出来,左表记录不足的地方均为null。
SELECT a.dname, b.* FROM dept a LEFT JOIN employee b ON a.deptnu = b.deptnu;
内连接、联合查询
内连接:获取两个表中字段匹配关系的记录
语法:inner join 表名 on 条件
-- 尽量使用第一种内连接写法,效率更加高效
-- 第一种写法
SELECT
a.dname,
b.*
FROM
dept a
INNER JOIN employee b ON a.deptnu = b.deptnu
AND b.ename = '张飞';
-- 第二种写法
SELECT
a.dname,
b.*
FROM
dept a,
employee b
WHERE
a.deptnu = b.deptnu
AND b.ename = '张飞';
联合查询:就是把多个查询语句的查询结果结合在一起
语法:...union...(去重);...union all...(不去重)
union查询的注意事项
两个select语句的查询结果的字段数必须一致
通常也应该让两个查询语句的字段类型具有一致性
可以联合更多的查询结果
用到order by排序时,需要加上limit(加上最大条数就行),需要对字句用括号括起来
( SELECT * FROM employee a WHERE a.job = '销售员' ORDER BY a.sal LIMIT 99 ) UNION
( SELECT * FROM employee b WHERE b.job = '文员' ORDER BY b.sal LIMIT 99)
4.DCL(Data Control Language)数据控制语言
root用户指定ip登录
-- 查看root用户可以在哪台机器登录
SELECT `user`, `host` FROM mysql.`user` WHERE `user` = 'root';
-- 修改user表
UPDATE mysql.`user` SET `host` = '192.168.73.10' WHERE `user` = 'root';
-- 刷新权限
FLUSH PRIVILEGES;
修改用户密码三种方法
-- 第一种:SET PASSWORD FOR 用户@ip = PASSWORD ( '密码' );
SET PASSWORD FOR root = PASSWORD ( '123456' );
-- 第二种:mysqladmin -u用户 -p旧密码 password 新密码;
mysqladmin -uroot -p123456 password gen123;
-- 第三种:UPDATE mysql.`user` SET authentication_string = PASSWORD ( '密码' ) WHERE `user` = 用户 AND `host` = ip;
UPDATE mysql.`user` SET authentication_string = PASSWORD ( '111222' ) WHERE `user` = 'root' AND `host` = 'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
忘记密码:修改mysql服务器配置
修改配置文件:vim /etc/my.cnf,在[mysqld]下面加上skip-grant-tables(跳过权限的意思)
重启mysql服务器
mysql -uroot -p,无需密码登录
修改密码
修改配置文件还原
创建新用户并限制ip网段登录
创建用户:CREATE USER 'username' @'host' IDENTIFIED BY 'password';
username:创建的用户名
host:指定该用户在哪个主机上可以登录,如果是本地用户可用localhost;如果想让用户从任意远程主机登录,可以使用通配符%;也可以指定网段登录如120.%.%.%
password:该用户的登录密码,密码可以为空
CREATE USER 'username' @'%' IDENTIFIED BY '123456';
查看权限
SELECT * FROM mysql.`user` WHERE `user` = 'root';
SHOW GRANTS FOR 'username';
结果:GRANT USAGE ON *.* TO 'username'@'%'
USAGE:无权限
SHOW GRANTS FOR 'root';
结果:GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
WITH GRANT OPTION:表示这个用户拥有grant权限,即可以对其她用户授权
删除用户
DROP USER 'username';
DELETE FROM mysql.`user` WHERE `user` = 'username';
库表权限授权与回收
授权语法:grant 权限1, 权限2 ... on 数据库对象 to '用户'
ALL PRIVILEGES:代表所有权限
*.*:代表所有库所有表
-- 对已有用户进行授权
GRANT ALL PRIVILEGES ON *.* TO 'username';
-- 创建用户并授权
GRANT ALL PRIVILEGES ON test.* TO 'dog' @'%' IDENTIFIED BY '123456';
GRANT SELECT ON test.* TO 'cat' @'%' IDENTIFIED BY '123456';
回收语法:revoke 权限1, 权限2 ... on 数据库对象 from '用户'
REVOKE SELECT ON test.* FROM 'cat';
-- 删除用户(回收所有权限)
DELETE FROM mysql.`user` WHERE `user` = 'cat';
FLUSH PRIVILEGES;