中文条件 | SQL语句 |
2022/07/14 14:25 |
创建数据库,并指定字符集 | create database test3 character set UTF8; |
查询所有数据库的名称 | show databases; |
查询某个数据库的创建语句 | show create database test3; |
修改数据库的字符集 | alter database test2 character set GBK; |
删除数据库 | drop database test2 ; |
查询当前正在使用的数据库名称 | select database(); |
创建表 | create table student1( id int, name varchar(32), birthday date, money double(5,2) ); |
查询某个数据库中所有的表名称 | show tables; |
查询表结构 | desc student1; |
添加一列 | alter table teacher add jieshao varchar(50); |
修改列类型 | alter table teacher modify jieshao varchar(99); |
修改列名和类型 | alter table teacher change jieshao newjieshao varchar(999); |
删除指定列 | alter table teacher drop intro; |
修改表字符集 | alter table teacher charset gbk; |
修改表名 | rename table teacher to tch; |
删除表 | drop table tch; |
添加数据 | INSERT INTO student1(id,name,birthday) VALUES (1,'李四1','2018-11-11'); |
批量插入数据 | INSERT INTO student1(id,name,birthday) VALUES (1,'李四1','2020-11-11'), (2,'李四2','2020-11-11'), (3,'李四3','2020-11-11'), (4,'李四4','2020-11-11'); |
删除表中所有记录 | delete from student; |
先删除表在创建一个一摸一样的表 | TRUNCATE TABLE 表名; |
根据条件删除数据 | delete from student where id = 1; |
修改数据 | update student set name = '王五', birthday = '2020-12-20' where id = 2; |
查询完整语法,查询的结果是一个虚拟表 | select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定 |
例子:-- 创建表 | create table stu( id int, name varchar(20), chinese double, english double, math double ); |
例子-- 插入记录 | insert into stu(id,name,chinese,english,math) values(1,'tom',89,78,90); insert into stu(id,name,chinese,english,math) values(2,'jack',67,98,56); insert into stu(id,name,chinese,english,math) values(3,'jerry',87,78,77); insert into stu(id,name,chinese,english,math) values(4,'lucy',88,NULL,90); insert into stu(id,name,chinese,english,math) values(5,'james',82,84,77); insert into stu(id,name,chinese,english,math) values(6,'jack',55,85,45); insert into stu(id,name,chinese,english,math) values(7,'tom',89,65,30); |
查询所有记录 | select * from stu; |
查询表中所有学生的姓名和对应的语文成绩 | SELECT name,chinese FROM stu; |
查询表中学生姓名(去重) | SELECT DISTINCT name FROM stu; |
在所有学生数学分数上加10分特长分 | SELECT name,math+10 FROM stu; |
统计每个学生的总分(在做行运算时,null参与的运算,计算结果都为null #as 起别名 ) | SELECT name, chinese+IFNULL(english,0)+math as 总成绩 FROM stu; |
如果第一个expro1为null则自动修改为0 | IFNULL(expr1,expr2) |
AS的用法 | SELECT s.name FROM stu as s |
运算符: | iin 、is NULL、like、AND、OR、NOT |
条件查询前提 | |
CREATE TABLE stu1 ( id int, name varchar(20), age int, sex varchar(5), address varchar(100), math int, english int ); | INSERT INTO stu1(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78), (2,'马化腾',45,'女','深圳',98,87), (3,'马景涛',55,'男','香港',56,77), (4,'柳岩',20,'女','湖南',76,65), (5,'柳青',20,'男','湖南',86,NULL), (6,'刘德华',57,'男','香港',99,99), (7,'马德',22,'女','香港',99,99), (8,'德玛西亚',18,'男','南京',56,65); |
条件查询sql | |
查询math分数大于80分的学生 | SELECT * FROM student2 WHERE math > 80; |
查询english分数小于或等于80分的学生 | SELECT * FROM student2 WHERE english <= 80; |
查询age等于20岁的学生 | SELECT * FROM student2 WHERE age = 20; |
查询age不等于20岁的学生 | SELECT * FROM student2 WHERE age != 20; |
查询age大于35且性别为男的学生(两个条件同时满足) | SELECT * FROM student2 WHERE age > 35 AND sex = '男'; |
查询age大于35或性别为男的学生(两个条件其中一个满足) | SELECT * FROM student2 WHERE age > 35 OR sex = '男'; |
查询id是1或3或5的学生 | SELECT * FROM student2 WHERE id = 1 OR id =3 OR id = 5; -- in关键字 -- 再次查询id是1或3或5的学生 SELECT * FROM student2 WHERE id IN(1,3,5); |
查询id不是1或3或5的学生 | SELECT * FROM student2 WHERE id NOT IN(1,3,5); |
查询english成绩大于等于77,且小于等于87的学生 | SELECT * FROM student2 WHERE english >=77 AND english <=87; SELECT * FROM student2 WHERE english BETWEEN 77 AND 87; |
查询英语成绩为null的学生 | SELECT * FROM student2 WHERE english = NULL; -- null这哥们六亲不认... SELECT * FROM student2 WHERE english IS NULL; SELECT * FROM student2 WHERE english IS NOT NULL; |
查询姓马的学生 | SELECT * FROM student2 WHERE name LIKE '马%'; |
查询姓名中包含'德'字的学生 | SELECT * FROM student2 WHERE name LIKE '%德%'; |
查询姓马,且姓名有三个字的学生 | SELECT * FROM student2 WHERE name LIKE '马__'; |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |