DML与DCl的操作
DML操作
创建一个数据库名为liu
mysql> create database liu;
Query OK, 1 row affected (0.00 sec)
进入liu这个数据库
mysql> use liu;
Database changed
在liu这个数据库下创建一个名为liuyang的表
插入以下内容
mysql> create table liuyang (id int not null,name varchar(20),age tinyint(2));
;
Query OK, 0 rows affected (0.01 sec)
select * from database_name,table_name;//查询来自某个数据库下的某个表的所有内容
mysql> select * from liu.liuyang;
Empty set (0.00 sec)
为liuyang这个表插入几条数据
mysql> insert into liuyang(id,name,age)values(1,'zs',18),(2,'ww',19),(3,'ls',20),(4,'zl',21);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from liuyang;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zs | 18 |
| 2 | ww | 19 |
| 3 | ls | 20 |
| 4 | zl | 21 |
+----+------+------+
4 rows in set (0.01 sec)
as 字段别名，为了自己清楚的知道每一个字段存放的是什么内容
mysql> select name as 姓名,age as 年龄 from liuyang;
+--------+--------+
| 姓名 | 年龄 |
+--------+--------+
| zs | 18 |
| ww | 19 |
| ls | 20 |
| zl | 21 |
+--------+--------+
4 rows in set (0.00 sec)
where 条件判断语句，更好的找到自己需要的那一条记录 select * from table_name where age = 18
操作类型 常用操作符
操作符 >，<，>=，<=，=，!=
BETWEEN column# AND column#
LIKE：模糊匹配
RLIKE：基于正则表达式进行模式匹配
IS NOT NULL：非空
IS NULL：空
条件逻辑操作 AND,OR,NOT
mysql> select * from liuyang where age = 18;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zs | 18 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from liuyang where id > 1;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | ww | 19 |
| 3 | ls | 20 |
| 4 | zl | 21 |
+----+------+------+
3 rows in set (0.00 sec)
条件逻辑操作（或与非）
mysql> select * from liuyang where id = 1 or age > 19;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zs | 18 |
| 3 | ls | 20 |
| 4 | zl | 21 |
+----+------+------+
3 rows in set (0.00 sec)
order by 排序工具 desc 为降序 ASC升序（默认为升序）
mysql> select * from liuyang order by age asc;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | zs | 18 |
| 2 | ww | 19 |
| 3 | ls | 20 |
| 4 | zl | 21 |
+----+------+------+
4 rows in set (0.00 sec)
mysql> select * from liuyang order by age desc;
+----+------+------+
| id | name | age |
+----+------+------+
| 4 | zl | 21 |
| 3 | ls | 20 |
| 2 | ww | 19 |
| 1 | zs | 18 |
+----+------+------+
4 rows in set (0.00 sec) //跳过第一行，显示后三行
mysql> select * from liuyang order by age limit 1,3;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | ww | 19 |
| 3 | ls | 20 |
| 4 | zl | 21 |
+----+------+------+
3 rows in set (0.00 sec)
update语句 用于更新某一条记录 update table_name set 字段名 = 要修改内容 where age=18;
mysql> update liuyang set age = 18 where name = 'liu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from liuyang;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | liu | 18 |
| 2 | ww | 19 |
| 3 | ls | 20 |
| 4 | zl | 21 |
+----+------+------+
4 rows in set (0.00 sec)
delete语句 用于删除某条记录，或者某个表的内容，但是结构还在，而且删除的内容会保留在日志中可以恢复
删除来自刘洋这个表中的name字段为liu的记录
mysql> delete from liuyang where name = 'liu' ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from liuyang;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | ww | 19 |
| 3 | ls | 20 |
| 4 | zl | 21 |
+----+------+------+
3 rows in set (0.00 sec)
truncate语句 作用与delete相同，但是不会保留删除的内容，不能恢复。
mysql> truncate liuyang;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from liuyang;
Empty set (0.00 sec)
DCL操作
将runtime用户在本机上可以查看liu数据库下的所有表
mysql> grant all on liu.* to 'runtime'@'127.0.0.1' identified by 'Liuyang123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
show grants 查看当前登录用户有哪些权限。
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)
show grants for username。查看指定用户的权限信息
mysql> show grants for runtime@127.0.0.1;
+----------------------------------------------------------+
| Grants for runtime@127.0.0.1 |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'runtime'@'127.0.0.1' |
| GRANT ALL PRIVILEGES ON `liu`.* TO 'runtime'@'127.0.0.1' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
取消授权revoke
mysql> revoke all on *.* from 'runtime'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql远程链接工具
mysql> desc liu.runtime;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| age | tinyint(4) | NO | | NULL | |
| sex | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from liu.runtime;
+---------+----+-----+------+
| name | id | age | sex |
+---------+----+-----+------+
| liuyang | 1 | 18 | 1 |
+---------+----+-----+------+
1 row in set (0.00 sec)
mysql>
实战案例
1.搭建mysql服务
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)
3.查看下该新建的表有无内容（用select语句）
4.往新建的student表中插入数据（用insert语句）,结果如下所示：
+----+-------------+------+
| 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 | qiuxiaotian | 20 |
+----+-------------+------+
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的记录
mysql> create database liu;
Query OK, 1 row affected (0.00 sec)
mysql> create table student (id int not null,name varchar(20),age tinyint(2
;
Query OK, 0 rows affected (0.01 sec)
mysql> create table student (id int not null primary key auto_increment,name varchar(20),age tinyint(2));
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(20) | YES | | NULL | |
| age | tinyint(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | liuyang | 20 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 6 | lisi | NULL |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | qiuyi | 15 |
| 10 | runtime | 20 |
+----+----------+------+
10 rows in set (0.00 sec)
mysql> update student set age = 50 where name = 'lisi';
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 | liuyang | 20 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 6 | lisi | 50 |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | qiuyi | 15 |
| 10 | runtime | 20 |
+----+----------+------+
10 rows in set (0.00 sec)
mysql> select * from student order by age desc ;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 6 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 3 | liuyang | 20 |
| 10 | runtime | 20 |
| 9 | qiuyi | 15 |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
+----+----------+------+
10 rows in set (0.00 sec)
mysql> select * from student order by age limit 2,3;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 9 | qiuyi | 15 |
| 1 | tom | 20 |
| 10 | runtime | 20 |
+----+---------+------+
3 rows in set (0.00 sec)
mysql> select * from student order by age desc limit 0,4;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 6 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 2 | jerry | 23 |
+----+----------+------+
4 rows in set (0.00 sec)
mysql> select * from student where name = 'zhangsan';
+----+----------+------+
| id | name | age |
+----+----------+------+
| 5 | zhangsan | 26 |
| 11 | zhangsan | 20 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> select * from student where name = 'zhangsan' and age > 20;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 5 | zhangsan | 26 |
+----+----------+------+
1 row in set (0.00 sec)
mysql> select * from student where age >= 23 and age <= 30;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | jerry | 23 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
+----+----------+------+
3 rows in set (0.00 sec)
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 | liuyang | 25 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | NULL |
| 8 | cs | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | q | 20 |
+----+----------+------+
11 rows in set (0.00 sec)
mysql> delete from student where name = 'zhangsan' and age <= 20;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | liuyang | 25 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 7 | lisi | NULL |
| 8 | cs | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | q | 20 |
+----+----------+------+
10 rows in set (0.00 sec)
