《MySql必知必会》阅读梳理（Mysql基础语法大全）

使用到了两个表
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
username CHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
password CHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

CREATE TABLE information (
id int NOT NULL AUTO_INCREMENT,
class char(10) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

show

1.show database

现实数据库内容

2.show tables

显示当前数据库的表信息

3.show columns from [表名]

例如 show columns from user
显示这个表中每个字段的详细信息

4.describe [表名]

和show columns from [表名]效果一样

use

1.user [数据库名字]

例如 show login
选中使用哪个数据库

select

在表中查询数据

select [字段名] from [表名]

select id from user;

select * from user

select id,username from user

limit

限制语句。限制显示的条数和起点
###1.select [字段名] from [表名] limit [起点],[显示条数]
select * from user limit 1,3
显示从下标1开始的3条信息

2. select [字段名] from [表名] limit [显示条数] offset[起点]

select * from user limit 3 offset 1

3.select [字段名] from [表名] limit [显示条数]

select * from user limit 4

order by

排序(limit要写在 order by之后)

select [字段名] from [表名] order by [字段] [排序方式]

select * from user order by id(根据id降序排列)
默认为升序排列

select * from user order by id asc(根据id升序排列)

select * from user order by id desc(根据id降序排列)

select [字段名] from [表名] order by [字段] [排序方式] limit [起点],[显示条数]

select * from user order by id limit 1, 3(根据id降序排列 并从第2个元素开始显示3条数据)

select [字段名] from [表名] order by [字段1],[字段2] [排序方式]

根据多个字段排序，只有当字段1相等时才判断字段2.排序方式只关联前一个字段
select * from user order by password desc,id (先根据password降序排序，当password相等时根据id升序排列)
注意password是字符串

where

过滤数据(order by 和limit要在where之后)

select * from [表名] where[判断条件]

select * from user where username = “sea”

select * from user where username != “sea”
!=同<>

select * from user where username >= “sea”

select * from user where id between 2 and 4
2<=id<=4

select * from user where username is null;
空值检测,只显示username为null的数据

and

当两个条件都满足时才查询出来
select * from user where id = 1 and password !=“abc”

select * from user where id = 1 and password !=“123”

or

满足一个条件
select * from user where id = 1 or password !=“abc”

select * from user where id = 1 or password !=“123”

in

范围内查询

select * from [表名] where [字段] in(值1，值2。。。)

select * from user where id