文章目录
1. 删除
DROP TABLE table_name; #删除数据表
drop database database_name; #删除数据库
delete from 表名 where 删除条件; #删除表内数据
Eg:delete from Student where T_name='张三';
delete from 表名;#删除表全部数据
#delete 操作以后,使用 optimize table table_name 会立刻释放磁盘空间
truncate table 表名; #清除表内数据,保存表结构,立刻释放磁盘空间,用 truncate
Eg: truncate table student;
-
当不再需要表时,用
drop
当仍要保持表,但不需要表内数据时,用truncate
当要删除部分记录,用delete
-
delete 是数据操纵语言 DML 语句,操作完以后如果没有不想提交事务还可以回滚,truncate 和 drop 是数据定义语言DDL 语句,操作完马上生效,不能回滚,打个比方,delete 是发微信说分手,后悔还可以撤回,truncate 和 drop是直接扇耳光说滚,不能反悔。
-
执行的速度上,drop>truncate>delete,打个比方,drop 是神舟火箭,truncate 是和谐号动车,delete 是自行车。
2. 插入
INSERT INTO table_name
(field1,field2,field3,....,fieldN)
VALUES
(value1,value2,value3,....,valueN);
实例:
INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> ("学习 PHP", "菜鸟教程", NOW());
# NOW()定义为现在的日期
添加数据的时候可以规定列进行添加。
如果所有的列都要添加数据可以不规定列进行添加数据:
mysql> INSERT INTO runoob_tbl
-> VALUES
-> (0, "JAVA 教程", "RUNOOB.COM", '2016-05-06');
INSERT INTO names SELECT given_name,family_name FROM students;
#将students表中的所有given_name,family_name插入到names表中。
3.查询
SELECT column_name,column_name from table_name [WHERE Clause][LIMIT N][ OFFSET M];
#括号内表示可有可无
- 可以使用一个或多个表,表之间用逗号分开
- 设定查询条件:
WHERE
- SELECT 命令可以读取一条或者多条记录
- 你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
- 可以使用
LIMIT
属性来设定返回的记录数。 - 可以通过
OFFSET
指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。 - 可以通过
select * from table_name;
打印出完整的表格 - 写一个查询去找 titles 和 每个title的长度:
SELECT title,LENGTH(title) FROM movies;
注意LENGTH方法 - Write a query to find the titles and running time of each movie, the running time should be rounded to the nearest hour and given the alias rh_time.:
SELECT title, ROUND(running_time/60) AS rh_time FROM movies;
注意是ROUND方法,并且用AS来换名
/*websites 表名 NAME alexa url country 字段*/
SELECT * FROM websites; /* 查询表所有数据 */
SELECT NAME FROM websites; /* 查询表字段数据 */
SELECT * FROM websites where name = "广西"; /* 查询表字段下条件数据 */
SELECT * from websites where name like "_o%"; /* 模糊查询表下数据 */
SELECT * FROM websites where id BETWEEN "1" AND "5"; /* 查询表下字段范围数据 */
SELECT * FROM websites WHERE name in ("广西","百度"); /* 查询表字段下固定条件数据 */
SELECT DISTINCT country FROM Websites; /* 查询去重值 */
SELECT * FROM Websites WHERE country = "CN" AND alexa > 50; /*查询表下范围条件数据*/
SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* 查询表下条件不同值 */
SELECT * FROM Websites ORDER BY alexa; /* 查询表下值排序结果 */
SELECT * FROM Websites ORDER BY alexa DESC; /* 查询表下排序结果降序 */
SELECT * FROM Websites LIMIT 2; /* 查询表下范围数据 */
SELECT name as zzz from websites; /*别名查询表下数据*/
4.WHERE子句
可以使用AND 或 OR 指定一个或多个条件,类似于程序中的if语句
SELECT * from runoob_tbl WHERE runoob_author='myself';
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。
可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
WHERE子句里还可以用连接词AND,NOT,OR,其中AND要求两边都满足才正确
SELECT * from runoob_tbl WHERE BINARY runoob_author='myself';
group by
:对select查询出来的结果集按照某个字段或者表达式进行分组,获得一组组的集合,然后从每组中取出一个指定字段或者表达式的值。
having
:用于对where和group by查询出来的分组经行过滤,查出满足条件的分组结果。它是一个过滤声明,是在查询返回结果集以后对查询结果进行的过滤操作。
5. 更新
UPDATE table_name SET field1=new - value1, field2=new - value2 [WHERE Clause];
- 可以在WHERE中指明条件
- 当我们需要将字段中的特定字符串批量修改为其他字符串时,可已使用以下操作:
UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') [WHERE Clause]
实例将更新 runoob_id 为 3 的runoob_title 字段值的 “C++” 替换为 “Python”:
UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') where runoob_id = 3;
6. LIKE子句 模糊匹配
SELECT field1,field2,...fieldN FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue';
-
LIKE 子句中使用百分号 %字符来表示任意字符(包含0个),类似于 UNIX 或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的,即可以用LIKE代替=号 -
符号‘’则表示一个字符,如:‘a’ //三位且中间字母是a的,如果求中间为下划线,则需要转义字符’\’:
last_name LIKE '_\_'
。其他的符号例如@,#.$,a,b,c等也可以做转义字符,不过最后要加个ESCAPE ‘符号’,eg:last_name LIKE '_a_' ESCAPE 'a' ;
-
用 AND 或 OR 指定多个条件
-
LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
-
不一定要在 ELECT 句中才可以用 LIKE ,其他例如 UPDATE 也可以
SELECT * from runoob_tbl WHERE runoob_author LIKE '%Mary';
- like 匹配/模糊匹配,会与 % 和 _ 结合使用。
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
#例子
#查询包含 java 字段的信息。
SELECT * FROM position WHERE name LIKE '%java%';
- 在 where like 的条件查询中,SQL 提供了四种匹配方式
-
%
:表示任意 0 个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。 -
_
:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。 -
[ ]
:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。 -
[^]
:表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故,导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现,而把特殊字符用 “[ ]” 括起便可正常查询。
- mysql的like查询默认是不区分大小写的
select * from table_name where a like 'a%'
select * from table_name where a like 'A%'
#查询结果一样
可以用binary 关键字来区分大小写
SELECT* FROM table_name WHERE binary a like 'a%'
SELECT * FROM table_name WHERE binary a like 'A%'
#可以在建表时,加以标识binary,也可实现like查询时区分大小写
create table table_name(
a varchar(20) binary
)
- 如果查%或者_,使用 escape,转义字符后面的%或_就不作为通配符了,注意前面没有转义字符的%和_仍然起通配符作用
select user from table_name where username like '%hua/%%' escape '/';
7. UNION 操作符 组合
用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
#语法
SELECT expression1, expression2, ... expression_n FROM table1_name [WHERE condition]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n FROM table2_name [WHERE condition];
- expression1, expression2, … expression_n:要检索的列,也就是最后打印出表格中会打印出的属性。
上面语法中打括号 [ ]表示是可选的,即可有可无 DISTINCT
:删除重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。ALL
: 返回所有结果集,包含重复数据。
#实例:
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country; #表示按country顺序排序
8.排序 ORDER BY 子句
使用 ORDER BY 子句将查询数据排序后再返回数据:
#语法:
SELECT field1,field2,field3,...fieldN FROM
table_name1,table_name2,table_name3,....table_nameN
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]];
- 可以使用
ASC
或DESC
关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。 - 可以添加 WHERE…LIKE 子句来设置条件
#实例:查询后的数据按 submission_date 字段的升序排列后返回。
SELECT * from runoob_tbl ORDER BY submission_date ASC;
按多个字段排序:查询员工信息,先按工资升序,再按员工编号降序
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;
9.RIGHT LEFT
RIGHT(str,2):查找字符串最右边的两个字符。同理LEFT查找
最左边的
SELECT * FROM employees WHERE RIGHT(name, 2)= "ll";
查找employees表中的name属性的最后两个字符,并将它们与“ll”进行比较。
10.LENGTH
LENGTH函数计算字符串的长度。这和编程是一样的,它计算字符串中的所有字符
SELECT * FROM employees WHERE LENGTH(title)=7;
11. 时间日期函数
CURDATE
:返回当前日期。
此函数也可用于目标列表。
例如:SELECT CURDATE(); 将在表中返回当前日期。
SELECT * FROM employees WHERE join_date = CURDATE();
找到今天被雇佣的所有员工的列表CURTIME
:返回当前时间。
此函数也可用于目标列表。例如:SELECT CURTIME();会返回表中的当前时间
SELECT * FROM appointments WHERE date = CURDATE() AND end time < CURTIME();
找到今天已经完成的所有约会议的列表NOW
:NOW函数类似于CURDATE和CURTIME的组合,它返回的不是日期对象或时间对象,而是DATETIME对象。这既适用于目标列表,也适用于查询的WHERE子句。DATEDIFF
:计算两个DATE或DATETIME对象之间的天数。语法是DATEDIFF(date1, date2)。
SELECT * FROM employees WHERE DATEDIFF(CURDATE(), join_date ) < 90;
找出在这工作少于90天的员工
SELECT name, DATEDIFF(CURDATE(), join_date) FROM employees;
找出所有员工的名字以及在这工作了多少天
12. 其他
sum
SELECT name, salary/12 FROM employees;
已知年薪,可以找出月薪,直接除以12
然而可能得到小数如7343.12,此时可以用ROUND函数来四舍五入结果
SELECT name, ROUND(salary/12) FROM employees;
SUM:求出总和
AS:换名称
SELECT SUM(payment_amount) AS total_income FROM payments;
求出payment_amount 的总和并用total_income 表示
LIMIT
语法:LIMIT num,numA
第一个num表示开始处,第二个表示限制的数量,限制要查找的那个的数量
LIMIT后如果接一个数据则表示限制的数量
ORDER BY payment_amount DESC LIMIT 5
SELECT given_name,family_name FROM guests WHERE start_date <="2020-02-14" AND end_date >"2020-02-14" ORDER BY family_name LIMIT 100,100;
BETWEEN… AND…
语法:BETWEEN....AND.....
WHERE pay_date BETWEEN "2020-01-01" AND "2020-01-31";
条件为在2020-01-01到2020-01-31之间