SQL语言
SQL语言笔记
SQL语言分类
-
SQL（Structured Query Language 即结构化查询语言）
-
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。
分类
-
DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER #DDL（data definition language）：
-
DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE #DML（data manipulation language）：
-
DQL语句 数据库查询语言： 查询数据SELECT #DQL（Data Query Language ）数据查询语言 SELECT
-
DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE #DCL（Data Control Language）：
Mysql默认数据库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)
-
information_schema： 虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等
-
performance_schema： 主要存储数据库服务器的性能参数
- 提供进程等待的详细信息，包括锁、互斥变量、文件信息；
- 保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
- 对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期
-
mysql： 授权库，主要存储系统用户的权限信息
-
sys： 主要存储数据库服务器的性能参数
- SYS database中，可以获取mem page、io 、latch/mutex/lock等各种性能数据，方便做peformance turning 和 troubleshooting。比如可以方便获取2个sql发生 lock block，用户占用的资源等信息。
表
- 记录的管理单元
记录（行）
- 信息的载体，字段的管理单元
- 例如：张三，男，23，云计算工程师，月薪25K
字段（列）
-
字段名，字段类型（长度），字段约束组成（可选）
-
姓名，文字，不能为空
-
张三
约束
- 唯一，不能为空，自动增长
DDL
DDL-库
创建数据库
语法
-
CREATE DATABASE 数据库名;
-
mysql> create database test; Query OK, 1 row affected (0.04 sec) mysql> mysql> show databases; #查看所有库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql>
数据库名称要求
- 区分大小写
- 唯一性
- 不能使用关键字如 create select
- 不能单独使用数字和特殊符号“如-"
- 正常的：用拼音或单词即可。
选择/进入数据库
语法
- USE 数据库名
mysql> use test; #进入数据库
Database changed
mysql> select database(); #调用函数，查询当前库
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql>
删除数据库
语法
- DROP DATABASE 数据库名;
mysql> drop database test; #删除数据库
Query OK, 0 rows affected (0.05 sec)
mysql> show databases; #查看是否删除成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
数据库在系统中的位置
- /var/lib/mysql/
[root@mysql ~] ls /var/lib/mysql
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql mysql.sock.lock private_key.pem server-cert.pem sys
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 ibtmp1 mysql.sock performance_schema public_key.pem server-key.pem
[root@mysql ~]#
- 数据库的实体
- 清理
- 备份
数据类型
数据类型种类
- 数值类型
- 整数类型 int
- 浮点数类型 float #小数
- 字符串类型
- 字符系列 CHAR和VARCHAR #char的长度不可变。 #varchar的长度可以增加
- 枚举类型 ENUM #单选
- 集合类型 SET #多选
- 时间和日期类型
- 年 YEAR
- 日期 DATE
- 时间 TIME
- 日期和时间 DATETIME
DDL-表
创建目的
- 表是数据库存储数据的基本单位
- 表由若干个字段（列）组成
- 主要用来存储数据记录（行）
创建1列的表格-id
mysql> show databases; #查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test; #进入数据库
Database changed
mysql> create table t1 (id int); #创建表名为t1，字段名为id 类型为int int‘整数’
Query OK, 0 rows affected (0.05 sec)
mysql> show tables; #查看表
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> insert into t1 values (1); #往t1表插入数据为‘1’
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1; #查询t1表里全部数据
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
mysql> drop table t1; #删除t1表
Query OK, 0 rows affected (0.02 sec)
mysql>
创建2列的表格-id和name
mysql> create table t2 (id int,name varchar(20)); #创建表为‘t2’ 字段分别是id和‘name’
Query OK, 0 rows affected (0.05 sec)
mysql> desc t2; #查询表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | TYPE：类型 Field：字段
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> show tables; #查看表
+----------------+
| Tables_in_test |
+----------------+
| t2 |
+----------------+
1 row in set (0.00 sec)
mysql> insert into t2 values (1,"lisi"); #往t2表插入数据 字符必须加引号（转义符）
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2; #查询t2表
+------+------+
| id | name |
+------+------+
| 1 | lisi |
+------+------+
1 row in set (0.00 sec)
mysql>
创建4列的表格-id、name、sex、age
mysql> create table t5(id int,name varchar(20),sex enum('m','f'),age int); #创建表名为t5 字段分别是id，name，sex，age
#sex类型为枚举 m代表‘男’ f代表‘女’
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql>
mysql> desc t5; #查询t5的表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | enum('m','f') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into t5 values (1,'zhangsan','m',33)； #插入数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 values (1,'lisi','f',33)； #插入数据
Query OK, 1 row affected (0.01 sec)
mysql> insert into t5 values (1,'wangwu','f',50); #插入数据
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from t5; #查看表
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhangsan | m | 33 |
| 1 | lisi | f | 33 |
| 1 | wangwu | f | 50 |
+------+----------+------+------+
3 rows in set (0.01 sec)
mysql>
DML
插入insert into
部分插入
需要指明要插入的数据是在一个字段
语法
- INSERT INTO 表名（列名，列名） VALUES (值1,值2);
mysql> insert into t5 (id ,sex) values (5,"f");
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+-----------+------+------+
| id | name | sex | age |
+------+-----------+------+------+
| 1 | zhangsan | m | 33 |
| 1 | lisi | f | 33 |
| 1 | 刘备 | f | 50 |
| 5 | NULL | f | NULL |
+------+-----------+------+------+
5 rows in set (0.00 sec)
mysql> insert into t5 (id,name) values (6," 张飞");
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+-----------+------+------+
| id | name | sex | age |
+------+-----------+------+------+
| 1 | zhangsan | m | 33 |
| 1 | lisi | f | 33 |
| 1 | wangwu | f | 50 |
| 1 | 刘备 | f | 50 |
| 5 | NULL | f | NULL |
| 6 | 张飞 | NULL | NULL |
+------+-----------+------+------+
6 rows in set (0.00 sec)
完整插入
不需要指明哪个字段，但是要注意的是需要把数据按照字段类型插入
语法
- INSERT INTO 表名 VALUES (值1,值2,值3…值n);
mysql> insert into t5 values (7,"关羽","m",70);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+-----------+------+------+
| id | name | sex | age |
+------+-----------+------+------+
| 1 | zhangsan | m | 33 |
| 1 | lisi | f | 33 |
| 1 | wangwu | f | 50 |
| 1 | 刘备 | f | 50 |
| 5 | NULL | f | NULL |
| 6 | 张飞 | NULL | NULL |
| 7 | 关羽 | m | 70 |
+------+-----------+------+------+
7 rows in set (0.00 sec)
mysql>
更新-update
语法
- UPDATE 表名 SET 列名=值 WHERE CONDITION;
更新数据
mysql> update t5 set id=2 where name="lisi";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t5;
+------+-----------+------+------+
| id | name | sex | age |
+------+-----------+------+------+
| 1 | zhangsan | m | 33 |
| 2 | lisi | f | 33 |
| 1 | wangwu | f | 50 |
| 1 | 刘备 | f | 50 |
| 5 | NULL | f | NULL |
| 6 | 张飞 | NULL | NULL |
| 7 | 关羽 | m | 70 |
+------+-----------+------+------+
7 rows in set (0.00 sec)
mysql>
更新Mysql密码-root
#修改mysql数据库管理员root账户的密码。
mysql> update mysql.user set authentication_string=password("P@ssw0rd1234") where user="root";
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges; #刷新
Query OK, 0 rows affected (0.02 sec)
[root@mysql ~] mysql -uroot -pP@ssw0rd1234; #使用修改之后的密码登陆
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
删除数据-delete
语法
删除一行数据
- DELETE FROM 表名 WHERE CONDITION;
mysql> select * from ccc.t5;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhangsan | m | 33 |
| 2 | lisi | f | 33 |
| 1 | wangwu | f | 50 |
| 4 | 刘备 | f | 50 |
| 5 | NULL | f | NULL |
| 6 | 张飞 | NULL | NULL |
| 7 | 关羽 | m | 70 |
+------+----------+------+------+
7 rows in set (0.00 sec)
mysql>
mysql>
mysql> delete from ccc.t5 where name='wangwu'; #删除name字段为wangwu的行
Query OK, 1 row affected (0.00 sec)
mysql> select * from ccc.t5;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhangsan | m | 33 |
| 2 | lisi | f | 33 |
| 4 | 刘备 | f | 50 |
| 5 | NULL | f | NULL |
| 6 | 张飞 | NULL | NULL |
| 7 | 关羽 | m | 70 |
+------+----------+------+------+
6 rows in set (0.00 sec)
mysql>
删除多行数据
mysql> select * from ccc.t5;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhangsan | m | 33 |
| 2 | lisi | f | 33 |
| 4 | 刘备 | f | 50 |
| 5 | NULL | f | NULL |
| 6 | 张飞 | NULL | NULL |
| 7 | 关羽 | m | 70 |
+------+----------+------+------+
6 rows in set (0.00 sec)
mysql> delete from ccc.t5 where name='zhangsan' or id=5; #若要删除多条，可用范围条件‘or’
Query OK, 2 rows affected (0.01 sec)
mysql> select * from ccc.t5;
+------+--------+------+------+
| id | name | sex | age |
+------+--------+------+------+
| 2 | lisi | f | 33 |
| 4 | 刘备 | f | 50 |
| 6 | 张飞 | NULL | NULL |
| 7 | 关羽 | m | 70 |
+------+--------+------+------+
4 rows in set (0.00 sec)
mysql>
DQL
准备环境
准备一张表，包含三列信息
- id int 序号
- name varchar 姓名
- age int 年龄
mysql> create table t3 (id int,name varchar(20),age int); #创建表为‘t3’
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; #查看当然数据库下所有表
+---------------+
| Tables_in_ccc |
+---------------+
| t3 |
| t5 |
+---------------+
2 rows in set (0.00 sec)
mysql> insert into t3 values (1,"zhangsan",23); #往‘t3’表插入数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values (2,"lisi",24); #往‘t3’表插入数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values (3,"wangwu",18); #往‘t3’表插入数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3; #查询’t3‘表里的内容
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 23 |
| 2 | lisi | 24 |
| 3 | wangwu | 18 |
+------+----------+------+
3 rows in set (0.00 sec)
mysql>
#创建数据库
mysql> create database company;
Query OK, 1 row affected (0.00 sec)
#进入数据库
mysql> use company;
Database changed
mysql> CREATE TABLE company.employee5(
-> id int primary key AUTO_INCREMENT not null,
-> name varchar(30) not null,
-> sex enum('male','female') default 'male' not null,
-> hire_date date not null,
-> post varchar(50) not null,
-> job_description varchar(100),
-> salary double(15,2) not null,
-> office int,
-> dep_id int
-> );
Query OK, 0 rows affected (0.02 sec)
#查看表结构
mysql> desc employee5;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+-----------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
#插入数据
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
-> ('jack','male','20180202','instructor','teach',5000,501,100),
-> ('tom','male','20180203','instructor','teach',5500,501,100),
-> ('robin','male','20180202','instructor','teach',8000,501,100),
-> ('alice','female','20180202','instructor','teach',7200,501,100),
-> ('aofa','male','20180202','hr','hrcc',600,502,101),
-> ('harry','male','20180202','hr',NULL,6000,502,101),
-> ('emma','female','20180206','sale','salecc',20000,503,102),
-> ('christine','female','20180205','sale','salecc',2200,503,102),
-> ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
-> ('gougou','male','20180205','sale','',2200,503,102);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
#查看表里内容
mysql> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.04 sec)
mysql>
简单查询
查看所有列
语法
-
SELECT * FROM 表名;
-
前提是需要进入数据库，不进入数据库，就请输入库名
mysql> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.04 sec)
查看部分列
语法
- SELECT 列1，列2，列3 FROM 表名;
mysql> select id,sex,post from employee5;
+----+--------+------------+
| id | sex | post |
+----+--------+------------+
| 1 | male | instructor |
| 2 | male | instructor |
| 3 | male | instructor |
| 4 | female | instructor |
| 5 | male | hr |
| 6 | male | hr |
| 7 | female | sale |
| 8 | female | sale |
| 9 | male | sale |
| 10 | male | sale |
+----+--------+------------+
10 rows in set (0.03 sec)
通过四则运算查询
mysql> select id,sex,salary,salary*14 from employee5; #这里*代表乘
+----+--------+----------+-----------+
| id | sex | salary | salary*14 |
+----+--------+----------+-----------+
| 1 | male | 5000.00 | 70000.00 |
| 2 | male | 5500.00 | 77000.00 |
| 3 | male | 8000.00 | 112000.00 |
| 4 | female | 7200.00 | 100800.00 |
| 5 | male | 600.00 | 8400.00 |
| 6 | male | 6000.00 | 84000.00 |
| 7 | female | 20000.00 | 280000.00 |
| 8 | female | 2200.00 | 30800.00 |
| 9 | male | 2200.00 | 30800.00 |
| 10 | male | 2200.00 | 30800.00 |
+----+--------+----------+-----------+
10 rows in set (0.00 sec)
条件查询
单条件查询where
查询hr部门的员工姓名
语法
- select 列名，列名，from 表名 where 列名=条件；
mysql> select name,post from employee5 where post='hr';
+-------+------+
| name | post |
+-------+------+
| aofa | hr |
| harry | hr |
+-------+------+
2 rows in set (0.00 sec)
多条件查询and/or
查询hr部门的员工姓名，并且工资大于1000
语法
- select 列名，列名 from 表名 where 部门字段=’条件‘ and 工资字段>1000;
mysql> select name,salary from employee5 where post='hr' and salary>1000;
+-------+---------+
| name | salary |
+-------+---------+
| harry | 6000.00 |
+-------+---------+
1 row in set (0.00 sec)
查询所有部门的员工姓名，并且工资是6000或者8000的员工
语法
- select 列名，列名 from 表名 where 工资字段=6000 or 工资字段=8000；
mysql> select name,salary from employee5 where salary=6000 or salary=8000;
+-------+---------+
| name | salary |
+-------+---------+
| robin | 8000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)
关键字between and 在什么之间
查一查薪资在5000到15000
语法
- select * from 表名 where 工资字段 between 5000 and 15000；
mysql> select * from employee5 where salary between 5000 and 15000;
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
5 rows in set (0.00 sec)
不在5000~15000呢？请使用NOT
语法
- select * from 表名 where 工资字段 not between 5000 and 15000；
mysql> select * from employee5 where salary Not between 5000 and 15000;
+----+-----------+--------+------------+------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------+-----------------+----------+--------+--------+
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-----------+--------+------------+------+-----------------+----------+--------+--------+
5 rows in set (0.00 sec)
关键字IN集合查询
工资可能是4000，也可能是5000，还有可能是9000
语法
- select 列名，列名 from 表名 where 工资字段 in （条件）；
mysql> select name,salary from employee5 where salary in (4000,5000,9000);
+------+---------+
| name | salary |
+------+---------+
| jack | 5000.00 |
+------+---------+
1 row in set (0.00 sec)
工资可能不是4000，也可能是5000，还有可能是9000，怎么查
语法
- select 列名，列名 from 表名 where 工资字段 not in （条件）；
mysql> select name,salary from employee5 where salary not in (4000,5000,9000);
+-----------+----------+
| name | salary |
+-----------+----------+
| tom | 5500.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| aofa | 600.00 |
| harry | 6000.00 |
| emma | 20000.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
+-----------+----------+
9 rows in set (0.00 sec)
关键字IS NULL
没有岗位描述的，值为空
语法
- select * from 表名 where 岗位字段 is null；
mysql> select * from employee5 where job_description is null;
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
+----+--------+------+------------+------+-----------------+---------+--------+--------+
2 rows in set (0.01 sec)
值非空
语法
- select * from 表名 where 岗位字段 is not null；
mysql> select * from employee5 where job_description is not null;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
8 rows in set (0.00 sec)
关键字LIKE模糊查询
好像有个员工姓a
语法
- select * from 表名 where 姓名字段 (a%); 通配符’%’代表多个任意字符
mysql> select * from employee5 where name like 'a%';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
2 rows in set (0.00 sec)
mysql>
另一种写法
mysql> select * from employee5 where name like 'a___'; 通配符’_’代表1个任意字符 这里我写了3个所以只出现aofa
+----+------+------+------------+------+-----------------+--------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+------+------+------------+------+-----------------+--------+--------+--------+
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
+----+------+------+------------+------+-----------------+--------+--------+--------+
1 row in set (0.00 sec)
查询排序ORDER BY
以工资升序排列
语法
- select 列名，列名 from 表名 order by 工资字段 asc；
mysql> select name,salary from employee5 order by salary asc;
+-----------+----------+
| name | salary |
+-----------+----------+
| aofa | 600.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
| jack | 5000.00 |
| tom | 5500.00 |
| harry | 6000.00 |
| alice | 7200.00 |
| robin | 8000.00 |
| emma | 20000.00 |
+-----------+----------+
10 rows in set (0.02 sec)
以工资降序排列
语法
- select 列名，列名 from 表名 order by 工资字段 desc；
mysql> select name,salary from employee5 order by salary desc;
+-----------+----------+
| name | salary |
+-----------+----------+
| emma | 20000.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
| tom | 5500.00 |
| jack | 5000.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
| aofa | 600.00 |
+-----------+----------+
10 rows in set (0.00 sec)
工资最高的前五名
语法
- select 列名，列名 from 表名 order by 工资字段 desc limit 5；
mysql> select name,salary from employee5 order by salary desc limit 5;
+-------+----------+
| name | salary |
+-------+----------+
| emma | 20000.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
| tom | 5500.00 |
+-------+----------+
5 rows in set (0.00 sec)
DCL
权限级别
- Global level 所有库，所有表的权限
- Database level 某个数据库中的所有表的权限
- Table level 库中的某个表的权限
- Column level 表中的某个字段，的权限
Mysql用户管理
#创建用户
mysql> create user wangwu@'localhost' identified by 'P@ssw0rd';
Query OK, 0 rows affected (0.01 sec)
mysql> select user from mysql.user; #查看是否创建成功
+---------------+
| user |
+---------------+
| mysql.session |
| mysql.sys |
| root |
| wangwu |
+---------------+
4 rows in set (0.00 sec)
CREATE 创建（关键字） USER 用户（关键字） wangwu用户名称 @ 分隔符（关键字） ‘localhost’ 允许登录的主机 IDENTIFIED BY 身份认证（关键字） ‘P@ssw0rd’密码
#删除用户
mysql> drop user wangwu@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| mysql.session |
| mysql.sys |
| root |
+---------------+
3 rows in set (0.01 sec)
root用户修改密码
[root@mysql ~] mysqladmin -uroot -pP@ssw0rd1234 password 'P@ssw0rd' 采用mysqladmin方式修改
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@mysql ~]#
另一种方法
mysql> set password=password('P@ssw0rd1234'); 采用set方式修改
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges; 刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[root@mysql ~] mysql -uroot -pP@ssw0rd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
丢失root密码怎么办
修改Mysql启动设置文件
vim /etc/my.cnf
[mysqld]
#添加
skip-grant-tables
重启mysql程序
systemctl restart mysqld 跳过密码
登陆mysql
mysql -uroot
修改root用户密码
mysql> update mysql.user set authentication_string=password('P@ssw0rd123') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
修改完成之后需要把启动文件添加的指令注释掉
vi /etc/my.cnf
#skip-grant-tables
[root@mysql ~] mysql -uroot -pP@ssw0rd123 使用新密码登陆
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Mysql权限原理
语法
- grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option参数];
权限列表
-
all： 所有权限（不包括授权权限）
-
select,update 查询更新
-
*.* 所有库下的所有表
-
test.* test库下的所有表
-
web.stu_info test库下的stu_info表
-
grant SELECT (id), INSERT (name,age) ON mydb.mytbl to 'user8'@'localhost' identified by 'QianFeng@123';
-
192.168.99.% 192.168.99.1网段的所有主机
-
192.168.99.168 指定主机
-
localhost 指定本地
-
==with_option参数 GRANT OPTION 授权选项 #默认被授权用户没有给其他用户授权的权限，加入with_option参数就会被增加为其他用户授权的操作
赋权
授予admin3 对test库 所有的表，具有所有权限（不包含授权）
语法
- grant all on 库名.* to 用户名@'本地用户' identified by '密码';
- grant all on test.* to admin3@'localhost' 其实这个sql也是可以的前提是用户需要创建完成，上面语句是如果没有当前用户那么他会创建一个用户来应用这个权限
mysql> create user admin3@'localhost' identified by 'P@ssw0rd1234';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on test.* to admin3@'localhost' identified by 'P@ssw0rd1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| admin3 |
| mysql.session |
| mysql.sys |
| root |
+---------------+
4 rows in set (0.00 sec)
[root@mysql ~] mysql -uadmin3 -pP@ssw0rd1234 admin3登陆mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; 用户admin3登陆上来只能看见test库及默认information库
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
mysql> create database hhh;
ERROR 1044 (42000): Access denied for user 'admin3'@'localhost' to database 'hhh' 创建库说明没有权限
mysql>
回收权限
查看自己权限
语法
- 使用 show grants 或者 show grants\G
mysql> show grants;
+----------------------------------------------------------+
| Grants for admin3@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin3'@'localhost' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin3'@'localhost' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
查看别人的权限
语法
- show grants for 用户名/'localhost'；
mysql> show grants for admin3@'localhost';
+----------------------------------------------------------+
| Grants for admin3@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin3'@'localhost' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin3'@'localhost' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
用户权限回收
收回admin3的权限
语法
- REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’
mysql> revoke all on test.* from admin3@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for admin3@'localhost'; 查看是否成功
+--------------------------------------------+
| Grants for admin3@localhost |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'admin3'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)
注意
- 5.6版本之前，先revoke all privilege再drop user
- 5.7之后，直接drop user 也可以回收权限
