MySQL的配置
文章目录
- MySQL的配置
- 一.数据库
- 二. DDL操作
- 二. DML操作
- 三. DCL操作
- 四. 作业
- 2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：
- 3.查看下该新建的表有无内容（用select语句）
- 4.往新建的student表中插入数据（用insert语句），结果应如下所示：
- 5.修改lisi的年龄为50
- 6.以age字段降序排序
- 7.查询student表中年龄最小的3位同学跳过前2位
- 8.查询student表中年龄最大的4位同学
- 9.查询student表中名字叫zhangshan的记录
- 10.查询student表中名字叫zhangshan且年龄大于20岁的记录
- 11.查询student表中年龄在23到30之间的记录
- 12.修改wangwu的年龄为100
- 13.删除student中名字叫zhangshan且年龄小于等于20的记录
一.数据库
1 sql的类型：
- DDL：（Data Defination Language）数据定义语言
- DML：（Data Manipulation Language）数据操纵语言
- DCL：（Data Control Language）数据控制语言
2.sql语句
| sql语句类型 | 操作 |
|---|---|
| ddl | 创建 create 删除 drop 修改 alter |
| dml | 插入 insert 删除 delete 更新 update 查询 select |
| dcl | 授权 grant 移除授权 revoke |
二. DDL操作
1. 创建数据库，create
mysql> CREATE DATABASE mushuang; //创建数据库
Query OK, 1 row affected (0.00 sec)
- 查询表
mysql> show tables from syl;
+---------------+
| Tables_in_syl |
+---------------+
| mushuang |
| shuang |
+---------------+
2 rows in set (0.00 sec)
mysql> SHOW DATABASES; //查询数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mushuang |
| mysql |
| performance_schema |
| syl |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql>
2. 删除数据库，drop
mysql> DROP DATABASE mushuang;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| syl |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
3. 修改表的结构，alter
3.1 创建表
mysql> create table student(id int(11) not null primary key auto_increment,name varchar(50),sex varchar(10) not null,age tinyint(6));
Query OK, 0 rows affected (0.00 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| sex | varchar(10) | NO | | NULL | |
| age | tinyint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
- 查看表
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 7 | lisi | 50 |
| 9 | wangwu | 100 |
+----+----------+------+
5 rows in set (0.00 sec)
3.2 增加表的字段
-
Alter table 表名 add 字段名 列类型 列属性
-
向表增加一列/字段
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table student add sex varchar(6) not null;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | varchar(6) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql>
3.3 修改表
-
修改字段名
-
alter table 表名 change 原字段名 新字段名 列类型 列属性;
-
实例1.
-
mysql> alter table student change sex mumu varchar(6) not null; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | mumu | varchar(6) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> -
实例2
-
mysql> desc mushuang; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | sex | varchar(6) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table mushuang change name dahai varchar(100) unique; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc mushuang; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | dahai | varchar(100) | YES | UNI | NULL | | | age | tinyint(4) | YES | | NULL | | | sex | varchar(6) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> -
修改列类型
-
alter table 表名 modify 字段名 列类型 列属性;
-
实例1.
-
mysql> desc mushuang; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | mumu | varchar(6) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table mushuang modify mumu char(6); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc mushuang; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | mumu | char(6) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) -
实例2.
-
mysql> desc mushuang; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | dahai | varchar(100) | YES | UNI | NULL | | | age | tinyint(4) | YES | | NULL | | | sex | varchar(6) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table mushuang modify dahai char(50) unique; Query OK, 5 rows affected, 1 warning (0.01 sec) Records: 5 Duplicates: 0 Warnings: 1 mysql> desc mushuang; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | dahai | char(50) | YES | UNI | NULL | | | age | tinyint(4) | YES | | NULL | | | sex | varchar(6) | NO | | NULL | | +-------+------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> -
修改表名
-
alter table 表名 rename to 新表名;
-
mysql> alter table student rename to mushuang; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------+ | Tables_in_syl | +---------------+ | mushuang | +---------------+ 1 row in set (0.00 sec) -
mysql> alter table student rename to dayuhaitang; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------+ | Tables_in_syl | +---------------+ | dayuhaitang | | mushuang | +---------------+ 2 rows in set (0.00 sec) mysql>
3.4 删除字段名
-
Alter table 表名 drop 字段名;
-
mysql> desc mushuang; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | mumu | char(6) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table mushuang drop mumu; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc mushuang; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql>
4. 用户操作
4.1 mysql账户的组成
-
mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
-
host可以由IP地址和通配符组成
-
IP地址，192.168.232.128
-
通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符
4.2 创建数据库用户
CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
mysql> create user 'mushuang'@'192.168.232.1' identified by 'Run2060526!';
Query OK, 0 rows affected (0.00 sec)
mysql>
4.3 用创建的用户登录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yfgpknlj-1650459453075)(C:/Users/Administrator/AppData/Roaming/Typora/typora-user-images/image-20220420162959587.png)]
CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
mysql> create user 'shuang'@'192.168.232.132' identified by 'Run123456!';
Query OK, 0 rows affected (0.00 sec)
mysql>
[root@SYL4 ~]# mysql -ushuang -p'Run123456!' -h192.168.232.132;
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 17
Server version: 5.7.37 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>
4.4 删除用户
DROP USER 'username'@'host';
mysql> drop user 'mushuang'@'192.168.232.1'
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
二. DML操作
mysql> show databases;
+--------------------+
| Database |
+--------------------+
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| syl |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use syl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table student(id int(11) not null primary key auto_increment,name varchar(50),sex varchar(10) not null,age tinyint(6));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_syl |
+---------------+
| mushuang |
| shuang |
+---------------+
2 rows in set (0.00 sec)
mysql> desc shuang;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| sex | varchar(10) | NO | | NULL | |
| age | tinyint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table shuang drop sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
1. 增加，insert
INSERT INTO 表名 [(column_name,...)] VALUES|VALUE (value1,...),(...)；
- 插入一条记录
mysql> insert into shuang(name,age) value('tom',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from shuang;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | tom | 20 |
+----+------+------+
1 row in set (0.00 sec)
mysql>
- 插入多条记录
mysql> insert into shuang(name,age) values('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshan',20),('lisi',NULL),('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxxiaotian',20);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from shuang;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxxiaotian | 20 |
+----+--------------+------+
11 rows in set (0.00 sec)
mysql>
2. 修改，update
UPDATE 表名 SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
-
修改lisi的年龄为50
-
修改wangwu的年龄为45
mysql> update shuang set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update shuang set age = 45 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from shuang;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 45 |
| 10 | qiuyi | 15 |
| 11 | qiuxxiaotian | 20 |
+----+--------------+------+
11 rows in set (0.01 sec)
mysql>
3. 查找，select
- 字段column表示法
| 表示符 | 含义 |
|---|---|
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1 当表名很长时用别名代替 |
- 条件判断语句where
| 操作类型 | 含义 |
|---|---|
| 操作符 | >，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 IS NOT NULL：非空 IS NULL：空 |
| 条件逻辑操作 | AND OR NOT |
3.1 ORDER BY：排序，默认为升序（ASC）
- 以age字段进行升序排序,ORDER BY age；
mysql> select * from shuang order by age;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 8 | chenshuo | 10 |
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxxiaotian | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 5 | zhangshan | 26 |
| 4 | sean | 28 |
| 9 | wangwu | 45 |
| 7 | lisi | 50 |
+----+--------------+------+
11 rows in set (0.00 sec)
- 进行降序排序,ORDER BY age DESC
mysql> select * from shuang order by age desc;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 7 | lisi | 50 |
| 9 | wangwu | 45 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
+----+--------------+------+
11 rows in set (0.00 sec)
- 进行升序排序取前两个结果，ORDER BY age LIMIT 2
mysql> select * from shuang order by age limit 2;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 8 | chenshuo | 10 |
| 10 | qiuyi | 15 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql>
- 进行降序排序略过前两个结果，取后面的2个结果，ORDER BY age DESC LIMIT 2,2
mysql> select * from shuang order by age desc limit 2,2;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
2 rows in set (0.00 sec)
mysql>
3.2 查询表中名字叫zhangshan的记录，表中名字叫zhangshan且年龄大于20岁的记录,表中名字叫zhangshan且年龄小于等于20的记录
mysql> select * from shuang where name = 'zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.00 sec)
mysql> select * from shuang where name = 'zhangshan' and age > 20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
2.4 查询student表中年龄在23到30之间的记录
mysql> select * from shuang where age between 23 and 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.01 sec)
mysql>
4. 删除，delete,truncate
- delete 删除内容可恢复，会保持记录，结构不会删除
- truncate 删除数据无法恢复，结构不会删除
4.1 delete
- 删除student表中年龄在23到30之间的记录
mysql> select * from shuang where age between 23 and 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.01 sec)
mysql> delete from shuang where age between 23 and 30;
Query OK, 4 rows affected (0.00 sec)
- 删除第11条记录
mysql> select * from shuang;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 45 |
| 10 | qiuyi | 15 |
| 11 | qiuxxiaotian | 20 |
+----+--------------+------+
7 rows in set (0.00 sec)
mysql> delete from shuang where id = 11;
Query OK, 1 row affected (0.00 sec)
4.2 truncate
- 删除表mushuang
mysql> truncate mushuang;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mushuang;
Empty set (0.00 sec)
mysql> desc mushuang;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dahai | varchar(100) | YES | UNI | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | varchar(6) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
4.3 delete和truncate的区别
| 语句类型 | 特点 |
|---|---|
| delete | DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
| truncate | 删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值(1) 执行速度比DELETE快，使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用删除数据 不能用于加入了索引视图的表 |
三. DCL操作
1. 权限类型(priv_type)
- 数据权限
| 类型 | 含义 |
|---|---|
| all | 所有权限 |
| select | 读取权限 |
| insert | 插入权限 |
| update | 更新权限 |
| delete | 删除权限 |
2. 指定要操作的对象db_name.table_name
| 方式 | 含义 |
|---|---|
| 星点星 | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.table_name | 指定库的指定表 |
3. 创建授权
3.1 授权mushuang用户在192.168.232.1上远程登录访问syl数据库
GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];//host客户端的ip，所登录的ip
mysql> grant all on *.* to 'mushuang'@'192.168.232.1' identified by 'Runby 'Run123456!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| syl |
| sys |
+--------------------+
5 rows in set (0.00 sec)
3.2 授权mushuang用户在所有位置上远程登录访问syl数据库
mysql> grant all on *.* to 'mushuang'@'%' identified by 'Run123456!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
4. 查看授权
- 查看当前登录用户的授权信息
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
- 查看指定用户mushuang的授权信息
mysql> show grants for mushuang;
+-----------------------------------------------+
| Grants for mushuang@% |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mushuang'@'%' |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'mushuang'@'192.168.232.1';
+-----------------------------------------------------------+
| Grants for mushuang@192.168.232.1 |
+-----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mushuang'@'192.168.232.1' |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5. 取消授权
REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
5.1 取消授权mushuang用户在所有位置上远程登录访问syl数据库
mysql> revoke all on *.* from 'mushuang'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
四. 作业
2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> create database shenyunlong;
Query OK, 1 row affected (0.00 sec)
mysql> use shenyunlong;
Database changed
mysql> create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql>
3.查看下该新建的表有无内容（用select语句）
mysql> select * from student;
Empty set (0.00 sec)
mysql>
4.往新建的student表中插入数据（用insert语句），结果应如下所示：
mysql> select * from student;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxxiaotian | 20 |
+----+--------------+------+
11 rows in set (0.00 sec)
mysql> insert into student(name,age) values('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshan',20),('lisi',NULL),('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxxiaotian | 20 |
+----+--------------+------+
11 rows in set (0.00 sec)
mysql>
5.修改lisi的年龄为50
mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
6.以age字段降序排序
mysql> select * from student order by age desc;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+--------------+------+
11 rows in set (0.00 sec)
mysql>
7.查询student表中年龄最小的3位同学跳过前2位
mysql> select * from student order by age limit 2,3;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 11 | qiuxxiaotian | 20 |
+----+--------------+------+
3 rows in set (0.00 sec)
mysql>
8.查询student表中年龄最大的4位同学
mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>
9.查询student表中名字叫zhangshan的记录
mysql> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.00 sec)
10.查询student表中名字叫zhangshan且年龄大于20岁的记录
mysql> select * from student where name = 'zhangshan' and age > 20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
mysql>
11.查询student表中年龄在23到30之间的记录
mysql> select * from student where age between 23 and 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.00 sec)
mysql>
12.修改wangwu的年龄为100
mysql> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxxiaotian | 20 |
+----+--------------+------+
11 rows in set (0.00 sec)
mysql>
13.删除student中名字叫zhangshan且年龄小于等于20的记录
mysql> select * from student where name = 'zhangshan' and age <=20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 6 | zhangshan | 20 |
+----+-----------+------+
1 row in set (0.00 sec)
mysql> delete from student where name = 'zhangshan' and age <=20;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student where name = 'zhangshan' and age <=20;
Empty set (0.00 sec)
mysql>
4269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
