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 name,sum(math),sum(english) from t1;
+----------+-----------+--------------+
| name | sum(math) | sum(english) |
+----------+-----------+--------------+
| zhangsan | 215 | 252 |
+----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select name,avg(math),avg(english) from t1;
+----------+-----------+--------------+
| name | avg(math) | avg(english) |
+----------+-----------+--------------+
| zhangsan | 53.7500 | 63.0000 |
+----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select name,max(english) from t1;
+----------+--------------+
| name | max(english) |
+----------+--------------+
| zhangsan | 90 |
+----------+--------------+
1 row in set (0.00 sec)
mysql> select name,min(english) from t1;
+----------+--------------+
| name | min(english) |
+----------+--------------+
| zhangsan | 33 |
+----------+--------------+
1 row in set (0.00 sec)
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)