SQL基本练习

select * from tab_user;
– 删除id为5的
delete from tab_user where u_id =5;
– 删除表里的所有内容
delete from tab_user;
– 删除表
drop table 表名；
– 删除表里的所有记录
– 先drop table 再create
– TRUNCATE 要比delete的效率高
TRUNCATE table tab_user;

– 创建用户 192.168.1.146 客户的ip
create user lianghao@’192.168.1.146’ IDENTIFIED by ‘666666’;
– 授权
grant all on my814.* to lianghao@’192.168.1.146’;
– 撤销权限
revoke all on my814.* from lianghao@’192.168.1.146’;
– 删除用户
drop user lianghao@’192.168.1.146’;

CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);

INSERT INTO stu VALUES(‘S_1001’, ‘liuYi’, 35, ‘male’);
INSERT INTO stu VALUES(‘S_1002’, ‘chenEr’, 15, ‘female’);
INSERT INTO stu VALUES(‘S_1003’, ‘zhangSan’, 95, ‘male’);
INSERT INTO stu VALUES(‘S_1004’, ‘liSi’, 65, ‘female’);
INSERT INTO stu VALUES(‘S_1005’, ‘wangWu’, 55, ‘male’);
INSERT INTO stu VALUES(‘S_1006’, ‘zhaoLiu’, 75, ‘female’);
INSERT INTO stu VALUES(‘S_1007’, ‘sunQi’, 25, ‘male’);
INSERT INTO stu VALUES(‘S_1008’, ‘zhouBa’, 45, ‘female’);
INSERT INTO stu VALUES(‘S_1009’, ‘wuJiu’, 85, ‘male’);
INSERT INTO stu VALUES(‘S_1010’, ‘zhengShi’, 5, ‘female’);
INSERT INTO stu VALUES(‘S_1011’, ‘xxx’, NULL, NULL);

– 查询所有列
SELECT *
FROM stu;
– 查询指定列
SELECT sid,sname,age
FROM stu;
– 列起别名 as 可以省略

SELECT sid 学号,sname 姓名,age 年龄
FROM stu;
– 查看表结构
desc stu;
– 查询性别为female，并且年龄小于50的记录
select * from stu
WHERE gender =’female’
AND age<50;

– 查询学号为S_1001，或者姓名为liSi的记录

select * from stu
WHERE sid =’s_1001’
or sname =’liSi’;
– 查询学号为S_1001，S_1002，S_1003,s_1004的记录
select * from stu
WHERE sid =’s_1001’
OR sid =’s_1002’
OR sid =’s_1003’;
– 用集合 in
select * from stu
WHERE sid in (‘s_1001’,’s_1002’,’s_1003’);
– 查询age 为 15 25 35 45的人

select * from stu
where age in (15,25,35,45);

– 查询学号不是S_1001，S_1002，S_1003的记录

select * from stu
WHERE sid not in (‘s_1001’,’s_1002’,’s_1003’);
– 查询年龄为null的记录

select * from stu
where age is null;
– 查询年龄不为null的记录

select * from stu
where age is not null;
– 查询年龄在20到40之间的学生记录 （包含25 45）
select * from stu
where age>=25 and age<=45;

– between AND

select * from stu where age BETWEEN 25 and 45;– 从小到大 闭区间
– 查询性别非男的学生记录
select * from stu
where gender != ‘male’;

select * from stu
where gender <> ‘male’;

– 查询姓名不为null的学生记录
select * from stu
where sname is not null;
– 模糊查询
– 查询姓名由5个字母构成的学生记录
– 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
– 查询姓名以“z”开头的学生记录
– 查询姓名中第2个字母为“i”的学生记录
– 查询姓名中包含“a”字母的学生记录