基础SQL语句整理(mysql5.7下通过运行)

-- 查询所有数据库
SHOW DATABASES;
-- 查询名为mysql数据库的创建语句
SHOW CREATE DATABASE mysql;
-- 创建数据库db1
CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET utf8;
-- 删除数据库db1
DROP DATABASE IF EXISTS db1;
-- 查询数据库db1的字符集
SHOW CREATE DATABASE db1;
-- 查询当前正在使用的数据库
SELECT DATABASE();
-- 使用数据库db1
USE db1;
-- 创建一个表
CREATE TABLE student (
  snum INT,
  sname VARCHAR (20),
  ssex CHAR(1),
  sdate DATE
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 查询数据库中所有的表
SHOW TABLES;
-- 查询student的表结构
DESC student;
-- 查看数据库db1中表student的字符集
SHOW TABLE STATUS FROM db1 LIKE 'student';
-- 把表名s_stdent修改为student
ALTER TABLE s_student RENAME TO student;
-- 把表student的字符集修改为utf8
ALTER TABLE student CHARACTER SET utf8;
-- 给表student添加一个字段
ALTER TABLE student ADD address CHAR(100);
-- 把表student的字段address的类型进行修改
ALTER TABLE student MODIFY address VARCHAR(50);
-- 把表student的字段address的名字及类型进行修改
ALTER TABLE student CHANGE address saddress VARCHAR(100);
-- 把表student的字段saddress进行删除
ALTER TABLE student DROP saddress;
-- 复制表student到stu
CREATE TABLE stu LIKE student;
-- 删除表stu
DROP TABLE IF EXISTS stu;
-- 标准插入记录语句
INSERT INTO student(snum,sname,ssex,sdate) VALUES(1,'张三','男','1999-09-09');
-- 省略字段插入记录语句
INSERT INTO student VALUES(2,'李四','男','2000-09-09');
-- 省略部分字段插入记录语句
INSERT INTO student(snum,sname) VALUES(3,'王五');
-- 批量记录插入语句，也可以进行部分字段的批量插入
INSERT INTO student VALUES(4,'赵云','男','2001-09-09'),(5,'女神','女','2000-09-09');
-- 修改编号为4号的记录名字为马超
UPDATE student SET sname='马超' WHERE id=4;
-- 删除名字为王五的记录
DELETE FROM student WHERE sname='王五';
-- 删除ssex为女的所有记录
DELETE FROM student WHERE `ssex`='女';
-- 查询表student所有记录
SELECT * FROM student;
-- 查询表student全部记录并把字段用别名表示（省略AS省略单引号的方式）
SELECT snum 编号,sname 姓名,ssex 性别,sdate 生日 FROM student;
-- 给表student添加一个年龄字段
ALTER TABLE student ADD sage INT;
-- 常规条件查询
SELECT * FROM student WHERE ssex='男' AND sage<40;
SELECT * FROM student WHERE ssex='女' OR sage<40;
SELECT * FROM student WHERE ssex='女' OR (sage>20 AND sage<50);
SELECT * FROM student WHERE ssex='女' OR (sage BETWEEN 20 AND 50);
SELECT * FROM student WHERE snum=1 OR snum=2 OR snum=3;
SELECT * FROM student WHERE snum IN(1,2,3);
SELECT * FROM student WHERE sage IS NULL;
SELECT * FROM student WHERE sage IS NOT NULL;
-- 模糊条件查询（%表示多个任意字符，_表示一个任意字符）
SELECT * FROM student WHERE sname LIKE '%豪%';
SELECT * FROM student WHERE sname LIKE '豪%';
SELECT * FROM student WHERE sname LIKE '%豪';
SELECT * FROM student WHERE sname LIKE '豪_';
SELECT * FROM student WHERE sname LIKE '豪__';