--通过SET插入记录

INSERT [INTO] tb_name SET 字段名称=值，……；

INSERT user SET username='sadfl',age=9;

2>更新记录UPDATE

UPDATE tb_name SET 字段名称=值|EXP|DEFAULT [WHERE 条件]

不加条件会更新表中所有记录

--user表中第一用户年龄改为45

UPDATE user SET age=45 WHERE id=1;

UPDATE user SET age=45,username='iopo' WHERE id=1;

添加默认值

ALTER TABLE user ALTER age SET DEFAULT 90;

--将user表中第三个用户的年龄改为默认值

UPDATE user SET age=DEFAULT WHERE id=3;

3>删除记录DELETE

DELETE FROM tb_name [WHERE 条件]

--删除id为1

DELETE FROM user WHERE id=1;

--删除所有记录

DELETE FROM user;

ALTER TABLE user AUTO_INCREMENT=1;

重置AUTO_INCREMENT

5>彻底清空一个表TRUNCATE,清空表时不用加条件会重置AUTO_INCREMENT,

TRUNCATE TABLE tb_name;

TRUNCATE TABLE user;

查询数据SELECT

SELECT exp[,..] FROM tb_name

[WHERE 条件]

[GROUP BY 分组[HAVING 子句对分组结果进行二次筛选]]

[ORDER BY 排序]

[LIMIT 限制显示条数]

SELECT * FROM user;

*代表所有字段

SELECT 字段名称，字段名称 FROM tb_name;

SELECT username FROM user;

SELECT username,age FROM user;

SELECT 表名.字段名称，...FROM tb_name;

SELECT user.id,user.age,user.username FROM user;

给字段起别名，给表起别名

SELECT 字段名称 [AS] 别名，...FROM tb_name [AS] 别名

SELECT id AS '编号',username AS '用户名',age AS '年龄'FROM user;

别名时尽量不要省略AS

SELECT u.id,u.username,u.age FROM user AS u;

1》WHERE表达式

比较运算符

SELECT 字段名称...FROM tb_name

WHERE 条件；

比较运算符

> >= < <= = <=> != <>

--user表插入记录

INSERT user VALUES(11,'EWRR',15);

INSERT user VALUES(6,'EWR44R',14);

INSERT user VALUES(7,'EW56RR'，45);

INSERT user VALUES(8,'EUYUYR',55);

INSERT user VALUES(9,'EWRR',4);

INSERT user VALUES(12,'EWXR4R',5);

INSERT user VALUES(4,'EWSRR',9);

--用户名为'sad'

SELECT * FROM user WHERE username='EWRR';

SELECT * FROM user WHERE id=6;

--年龄大于18

SELECT * FROM user WHERE age>=18;

不等于

SELECT * FROM user WHERE age!=123;

SELECT * FROM user WHERE age<>123;

等于

SELECT * FROM user WHERE age<=>123;

//<=>可以检测某个值是否为空

INSERT user VALUES(36,'test1',NULL);

SELECT * FROM user WHERE age<=>NULL;

SELECT * FROM user WHERE age=NULL;

--通过IS NULL 或者IS NOT NULL检测是否为空

SELECT * FROM user WHERE age IS NULL;

SELECT * FROM user WHERE age IS NOT NULL;

--年龄18-28

BETWEEN...AND或者NOT BETWEEN...AND

SELECT * FROM user WHERE age BETWEEN 18 AND 28;

SELECT * FROM user WHERE age NOT BETWEEN 18 AND 28;

IN在集合中的形式

SELECT * FROM user WHERE age IN(18 ,12,23,35,28);

SELECT * FROM user WHERE age NOT IN(18 ,12,23,35,28);

通过LIKE实现模糊查询

%：代表0个1个或多个任意字符；

―：1个任意字符

--包含张/w的用户

SELECT * FROM user WHERE username LIKE '%张%';

SELECT * FROM user WHERE username LIKE '%W%';

--以张开头

SELECT * FROM user WHERE username LIKE '张%';

查询用户名长度为3位的用户

SELECT * FROM user WHERE username LIKE '___';

SELECT * FROM user WHERE username LIKE '_E%';

逻辑运算符

and or

用户名为king并且年龄为28的信息

SELECT * FROM user WHERE username='king' AND age=28;

用户名包含k或者年龄小于50的用户

SELECT * FROM user WHERE username LIKE '%K%' OR age<50;

2》分组查询

CREATE TABLE IF NOT EXISTS department(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

depname VARCHAR(20) NOT NULL UNIQUE);

INSERT department(depname) VALUES('摄影部');

INSERT department(depname) VALUES('视频部');

INSERT department(depname) VALUES('教学部');

INSERT department(depname) VALUES('督导部');

CREATE TABLE IF NOT EXISTS employee(

id INT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED DEFAULT 18,

addr VARCHAR(50) NOT NULL DEFAULT '北京',

salary FLOAT(6,2) NOT NULL DEFAULT 0,

sex ENUM('男','女','保密'),

depid TINYINT UNSIGNED

);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('张三',12,'上海',6234.56,'男',1);