CREATE TABLE user(
id int(11) PRIMARY KEY auto_increment,
name varchar(20) not null,
sex varchar(1),
phoneNumber VARCHAR(11),
info varchar(255) default ‘这个人很懒,还没有描述信息哦’
);
– DML数据操作
– 插入语句insert
– 多条插入
INSERT INTO user VALUES
(1003,‘Alice’,‘女’,‘123’,‘爱好运动,擅长电竞’),
(1004,‘Bob’,‘男’,‘123’,‘爱好运动,擅长电竞’),
(1005,‘Clover’,‘女’,‘123’,‘爱好运动,擅长电竞’);
– 单条插入
INSERT INTO user VALUE(1001,‘Alice’,‘女’,‘123’,‘爱好运动,擅长电竞’);
– 指定字段插入
INSERT INTO user(name,sex,phoneNumber) VALUE(‘Bob’,‘男’,‘234’);
INSERT INTO user(name,sex,phoneNumber) VALUES
(‘Test’,‘男’,‘234’),
(‘Test’,‘男’,‘234’),
(‘Test’,‘男’,‘234’);
– 删除数据(不改变表的规则)
delete from user where id=1008;
– 删除所有的 数据 (会改变表的特性 最明显的 是自增特性)
TRUNCATE TABLE user;
– 更新数据
update user set name=‘Alice’,sex=‘女’ where id=1;
– 查询数据select
SELECT * from user;
– 查询指定列
SELECT name,sex,phoneNumber FROM user;
– 条件查询 and 与or 的用法一样
select * from user where name=‘Test’ and sex=‘女’;
– 条件查询 > < = >= <= != 用法一样
select * from user where id>1006;
– null 与 not null 用法一样 (且注意不能用 =null 只能用is)
SELECT * from user where info is not null;
– 模糊查询like
SELECT * from user WHERE name like ‘%c%’ or info like ‘%爱好%’;
– _ 一个下划线代表一个字符
select * from user where name like ‘T_’;
– between a and b 查询区间[a,b](左闭右开 等价与条件语句的<= and >=)
select * from user where id BETWEEN 1003 and 1006;
– in包含集合中的一个(等价与条件语句 = or =)
SELECT * from user where name in(‘Alice’,‘Clover’);
– 排序,ASC升序,DESC降序。
select * from user ORDER BY name DESC;
select * from user ORDER BY name DESC,id DESC;
– 以列号来排序
select * from user ORDER BY 2 DESC,1 DESC;
select * from user ORDER BY name DESC,id DESC;
– 限定行数查询 limit (左开右闭 从3+1开始查 查询3行 查到第3+3+1行)
SELECT * from user limit 3,3;
– 给表,列 起别名
SELECT u.name,u.sex from user u;
– 起别名两种形式,as可省略,常量列 姓名
SELECT name as 姓名
,phoneNumber as 电话
from user;
SELECT name 姓名
,phoneNumber 电话
from user;