DML-MYSQL

最新推荐文章于 2024-02-22 06:45:00 发布

aaqq3561

最新推荐文章于 2024-02-22 06:45:00 发布

阅读量222

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/aaqq3561/article/details/41017007

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

DML---数据操作语言

insert 插入记录

insert into db.tbname set column='insert value',column1='insert value' ----指定插入数据

insert into db.tbname values(1,'a'),(2,'b) ------插入多条不过要注意的是，你要插入同列数对应的数据，不能小；

insert into db.tbname(cloumn) values('insert valus') -------这句你可以指定你要插入的列。

insert into db.tbname select * from db.tb1name -----将db.tb1name表的结果，插入到db.tbname (唯一值得注意的事表结构要相同)

update更新记录

update db.tbname set id=5 where name='test';

例子：

修改‘root’@‘localhost'的登录密码为123

update mysql.user set password=passowrd('123') where user='root' and host='localhost';

修改完之后用flush privileges 刷新权限表（写入到磁盘）

delete/truncate 删除记录

delete from mysql.user where user=''; -------删除mysql中的匿名语句

delete from tb2 -----不加条件删除表所有数据

truncate from t2 where id=2 -----报错不能指定条件删除一删除就全部删除

truncate t2; --------- 执行的是删除表所有数据

truncate 和delete 不同之处 delete是一行一行删除没删除一行都会写入事务日志，可以同过事务日志恢复，而truncate不会写日志，是不可逆操作

so 在生产环境里基本不用

通配符

% 匹配0个和任意多个字符

_ 匹配一个字符

like 模糊匹配 select * from tbname where cloumn like '%a%' 代表匹配有a字符

= 精确匹配

regexp (^ . .* ....)使用正则表达式来匹配 select * from tbname where cloumn regexp (^...) 正则

排序：

ordey by 排序

asc 升序排列结果 select * from tbname ordey by cloumn asc/desc;

desc 降序排列结果

group by 聚合

distinct 去除重复行

mysql> create database db02;
mysql> use db02
mysql> create table db02.t1(id int ,name varchar(50) ,math tinyint, english tinyint);

mysql> insert into db02.t1 set id=1,name='zhangsan',math=55,english=66;
mysql> insert into db02.t1 set id=2,name='lisi',math=66,english=77;
mysql> insert into db02.t1 set id=3,name='wangwu',math=65,english=30;
mysql> insert into db02.t1 set id=2,name='li04',math=88,english=99;
mysql> insert into db02.t1 set id=3,name='wang5',math=75,english=73;
mysql> insert into db02.t1 set id=4,name='zhao6',math=75,english=73;
mysql> insert into db02.t1 set id=5,name='liu3',math=85,english=43;

mysql> select * from db02.t1;  --查询表中所有的列对应的值（全表扫描）
mysql> select id,name from t1;       --查询表中指定列
mysql> select host,password,user from mysql.user;  --查询表中指定列
mysql> select host as 主机名,password as 密码,user as 用户名 from mysql.user; --给列取别名，增加可读性
mysql> select * from t1 where name='i';
mysql> select * from t1 where name like 'i';
mysql> select * from t1 where name like '%i%';      --模糊匹配
mysql> select * from t1 where name like '____';

mysql> select * from t1 where id=2;
mysql> select * from t1 where name='wang5'; --精确匹配
mysql> select * from t1 where english < 60;

mysql> select * from t1 where name regexp '.*[0-9]?.*' ; --支持正则表达式
mysql> select * from t1 where name regexp '.*[0-9]+.*' ; 任意数字一次或一次以上
mysql> select * from t1 where name regexp '.*[0-9]{2}.*' ;

mysql> select user,password,host from mysql.user where host regexp '^l'; --支持正则表达式
mysql> select user,password,host from mysql.user where host regexp '([0-9]{1,3}\.){3}([0-9]{1,3})';

排序
mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id asc; 升序

mysql> select * from t1 where name regexp '.*[0-9]?.*' order by id desc; 降序

去除重复行 distinct
mysql> select distinct id from t1 ;

聚合 group by 聚合以第一个为标准
mysql> select * from t1 group by id;

mysql> select * from t1 group by id having id <= 2;
gruop by 不能用where语句用having语句

使用select来调度mysql中的常见函数：
mysql> select current_user();
mysql> select current_time();
mysql> select current_date();
mysql> select count(*) from t1; --计算表中有多少条记录
mysql> select now();

合并列：
mysql> select concat(user,'    ',password) as 用户名和密码 from mysql.user;
mysql> select concat(user,'    ',password) as 用户名和密码 from mysql.user order by 用户名和密码 asc; --按照升序来排列结果
mysql> select concat(user,'    ',password) as 用户名和密码 from mysql.user order by 用户名和密码 desc; --降序排列

mysql> create table t3(path1 varchar(255),homedir varchar(255));
mysql> insert into t3 set path1='/www/uplooking',homedir='u01';
mysql> select concat(path1,'/', homedir) from t3;
mysql> select concat(path1,'/', homedir) as '家目录' from t3;
+--------------------+
| 家目录 |
+--------------------+
| /www/uplooking/u01 |
+--------------------+
1 row in set (0.00 sec)

mysql> select id,name as '姓名', math '数学成绩', english '英语成绩' from t1;

分页函数（limit）：
mysql> select * from t1 limit 10; --显示前10行
mysql> select * from t1 limit 10,10; --显示11至20行

mysql> select * from t1 limit 1; 显示第1行
mysql> select * from t1 limit 0,2; 显示第1,2行

mysql> select * from t1 limit 2,2;      显示3,4行
起始位置，偏移量第二行的下2行
mysql> select * from t1 limit 4,2;      显示5,6行
mysql> select * from t1 limit 6,2;      显示第7,8行

mysql> select * from t1 order by english desc limit 3;
显示英语前三名
mysql> select name,(math+english) as sum from t1 order by sum desc;
显示总分，以降序排列
mysql> select name,(math+english) as sum from t1 order by sum desc limit 1;
显示总分第一名
mysql> select name,max((math+english)) from t1;
显示总分第一名
mysql> select name,max(math+english) from t1 order by (math+english) desc;
显示总分第一名
mysql> select name,english from t1;

mysql> select max(english) from t1;
显示英语的最高分
mysql> select min(english) from t1;
显示英语的最低分

mysql> select name,english from t1 order by english desc limit 1\c
mysql> select name,english from t1 where english in (select max(english) from t1);
子查询
显示英语的最高分并显示姓名
select name,(math+english)/2 from t1 where (math+english)/2 in (select max((math+english)/2) from t1);
select name,(math+english)/2 from t1 where (math+english)/2 order by (math+english)/2 desc limit 1;
显示总成绩的平均分第一名

+ - * / mysql运算符
mysql> select 1 + 1, (10-1)/3, 2*2/2;

mysql> select 1 + '10a';
+-----------+
| 1 + '10a' |
+-----------+
| 11 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> select 1 + 'a10';
+-----------+
| 1 + 'a10' |
+-----------+
| 1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> select 1 = 2;
+-------+
| 1 = 2 |ENGINE=
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)

mysql> select 1 = 1;
+-------+
| 1 = 1 |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

mysql> select 1 + 1 from dual; －－dual表,俗称万能表

返回值1，为真

and or not 逻辑运算

1.and
mysql> select * from t1 where math >= 60 and english >= 60;

2.or
mysql> select * from t1 where math >= 60 or english >= 60; 其中一科及格

3.not

mysql> select * from t1 where math >= 60 and not english >= 60;
+------+--------+------+---------+
| id   | name   | math | english |
+------+--------+------+---------+
|    3 | wangwu |   65 |      30 |
|    5 | liu3   |   85 |      43 |
+------+--------+------+---------+
2 rows in set (0.00 sec)

sum() avg() max() min() count()

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

mysql> select max(english) - min(math) from t1; --英语的最高分与数学最低的差距
+--------------------------+
| max(english) - min(math) |
+--------------------------+
| 50 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t1 order by english desc limit 3;
+------+----------+------+---------+
| id   | name     | math | english |
+------+----------+------+---------+
|    1 | zhangsan |   70 |      90 |
|    4 | lisi02   |   55 |      69 |
|    2 | lisi     |   50 |      60 |
+------+----------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from t1 order by english asc limit 1,2; 显示第二名和第三名
+------+--------+------+---------+
| id   | name   | math | english |
+------+--------+------+---------+
|    4 | lisi02 |   55 |      69 |
|    2 | lisi   |   50 |      60 |
+------+--------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from db1.t1 where math >= 60 or english >=60 ;
+------+----------+------+---------+
| id   | name     | math | english |
+------+----------+------+---------+
|    1 | zhangsan |   70 |      90 |
|    2 | lisi     |   50 |      60 |
|    4 | lisi02   |   55 |      69 |
+------+----------+------+---------+
3 rows in set (0.00 sec)

记录复制表结构的复制

mysql> create table t3 like t1; --复制表结构

mysql> create database db03;
mysql> use db03
mysql> create table t1(id int,name varchar(50));
mysql> create table t2(id int,socre int);
mysql> insert into t1 set id=1,name='lee';
mysql> insert into t1 set id=2,name='zhang';
mysql> insert into t1 set id=4,name='wang';
mysql> insert into t2 set id=1,socre='90';
mysql> insert into t2 set id=2,socre='100';
mysql> insert into t2 set id=3,socre='90';
mysql> select * from t1;
+------+-------+
| id   | name |
+------+-------+
|    1 | lee   |
|    2 | zhang |
|    4 | wang |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+------+-------+
| id   | socre |
+------+-------+
|    1 |    90 |
|    2 |   100 |
|    3 |    90 |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from t1,t2;      笛卡尔集,2张表的乘方
+------+-------+------+-------+
| id   | name | id   | socre |
+------+-------+------+-------+
|    1 | lee   |    1 |    90 |
|    2 | zhang |    1 |    90 |
|    4 | wang |    1 |    90 |
|    1 | lee   |    2 |   100 |
|    2 | zhang |    2 |   100 |
|    4 | wang |    2 |   100 |
|    1 | lee   |    3 |    90 |
|    2 | zhang |    3 |    90 |
|    4 | wang |    3 |    90 |
+------+-------+------+-------+
9 rows in set (0.00 sec)

mysql> insert into t3 select * from t1; --复制记录
mysql> select * from t3;
+------+-------+
| id   | name |
+------+-------+
|    1 | lee   |
|    2 | zhang |
|    4 | wang |
+------+-------+
3 rows in set (0.00 sec)

多表查询
mysql> select t1.id,t1.name,t2.socre from t1,t2 where t1.id=t2.id;
+------+-------+-------+
| id   | name | socre |
+------+-------+-------+
|    1 | lee   |    90 |
|    2 | zhang |   100 |
+------+-------+-------+
2 rows in set (0.00 sec)

表的连接:纵向连接/内连接/左连接/右连接

常规查表:
mysql> select * from t1,t2;
mysql> select * from t1,t2 where t1.id=t2.id;
+------+-------+------+-------+
| id   | name | id   | socre |
+------+-------+------+-------+
|    1 | lee   |    1 |    90 |
|    2 | zhang |    2 |   100 |
+------+-------+------+-------+
2 rows in set (0.00 sec)

左连接（以左表为标准连接右表）:
letft join=left [outer] join

mysql> select * from t1 left outer join t2 ON t1.id=t2.id;
+------+-------+------+-------+
| id   | name | id   | socre |
+------+-------+------+-------+
|    1 | lee   |    1 |    90 |
|    2 | zhang |    2 |   100 |
|    4 | wang | NULL | NULL |
+------+-------+------+-------+
3 rows in set (0.00 sec)

右连接（以右表为标准连接左表）:
right join= right outer join
mysql> select * from t1 right outer join t2 ON t1.id=t2.id;
+------+-------+------+-------+
| id   | name | id   | socre |
+------+-------+------+-------+
|    1 | lee   |    1 |    90 |
|    2 | zhang |    2 |   100 |
| NULL | NULL |    3 |    90 |
+------+-------+------+-------+
3 rows in set (0.00 sec)

内连接:取多表之间的交集
mysql> select * from t1 inner join t2 on t1.id=t2.id;
+------+-------+------+-------+
| id   | name | id   | socre |
+------+-------+------+-------+
|    1 | lee   |    1 |    90 |
|    2 | zhang |    2 |   100 |
+------+-------+------+-------+
2 rows in set (0.00 sec)

纵向连接:
mysql> select * from t1 union select * from t2;5
+------+-------+
| id   | name |
+------+-------+
|    1 | lee   |
|    2 | zhang |
|    4 | wang |
|    1 | 90    |
|    2 | 100   |
|    3 | 90    |
+------+-------+
6 rows in set (0.00 sec)