mysql常见语法

mysql -u root -p mysql -u root -p -D 数据库名

'''1、创建一个school库2、运行SHOW DATABASES，会发现多了一个school库'''CREATE DATABASE school;

'''1、如果school库不存在，则创建school库，2、不使用IF NOT EXISTS，如果school库存在，会报库已存在的错误'''CREATE DATABASE IF NOT EXISTS school;

'''1、如果school库不存在，则创建school库，2、DEFAULT CHARACTER SET 设置字符集3、DEFAULT COLLATE 设置排序规则4、DEFAULT ENCRYPTION='N' 是否加密，否：N，是：Y，如果设置Y，则操作数据库需要密钥'''CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8mb3_general_ci DEFAULT ENCRYPTION='N';

'''删除school库'''DROP DATABASE school;

'''1、如果school库存在，则删除school库，2、不使用IF EXISTS，如果school库不存在，会报库不存在的错误'''DROP DATABASE IF EXISTS school;

'''切换到school库'''USE school

'''1、创建一个包含id、name字段的student表2、运行SHOW TABLES，会发现多了一个student表3、需要切换到要添加表的库，如没切换可以使用：school.student 创建表（在school库里创建student表）'''CREATE TABLE student(id INT,name VARCHAR(200));

'''1、如果student表不存在，则创建一个包含id、name字段的student表，2、不使用IF NOT EXISTS，如果tudent表存在，会报表已存在的错误'''CREATE TABLE IF NOT EXISTS student (id INT,name VARCHAR(200));

'''1、从student表中创建student_copy表2、*为通配符，如需要student表中部分字段，可直接使用字段名：id,name3、如果student表中有数据，会携带数据一起创建student_copy表'''CREATE TABLE student_copy SELECT * FROM student;

'''1、设置字符集DEFAULT CHARACTER SET2、设置储存引擎：ENGINE=InnoDB3、储存引擎分类：InnoDB、MyISAM、CSV、Archive、Memory'''CREATE TABLE student(id INT,name VARCHAR(200)) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

'''id添加描述学生id'''CREATE TABLE student(id INT COMMENT '学生id',name VARCHAR(200));

'''id未设置值泽默认为0'''CREATE TABLE student(id INT DEFAULT 0,name VARCHAR(200));

'''id可以设置为null'''CREATE TABLE student(id INT NULL,name VARCHAR(200));

'''id不能是NULL，可以和DEFAULT搭配使用'''CREATE TABLE student(id INT NOT NULL DEFAULT 0,name VARCHAR(200));

'''1、一个表只能有一个自增字段2、且必须为键，如UNIQUE KEY，PRIMARY KEY3、可以设置自增长起始值AUTO_INCREMENT=10，不设置从1开始自增4、不能保证连续递增，假如最新自增数据是id=10，又把该数据删除，那么再插入数据，id会是11，id=10的数据不会再创建'''CREATE TABLE student(id INT UNIQUE AUTO_INCREMENT,name VARCHAR(200)) AUTO_INCREMENT=10;

'''1、id小于四位的会自动补零，int(4)说明字段是4位数字2、加了ZEROSFILL，id：0001、0002、0003...，不加ZEROSFILL，id：1、2、3...'''CREATE TABLE student(id INT(4) ZEROFILL UNIQUE AUTO_INCREMENT,name VARCHAR(200));

'''1、id为无符号字段，不能添加负数2、INT类型，有符号值：-2147683648 到2147683647无符号值：0到4294967295（有符号最大值*2）'''CREATE TABLE student(id INT UNSIGNED ZEROFILL,name VARCHAR(255));

'''检查约束id，大于10，小于20；不在这个范围，插入数据时报错'''CREATE TABLE student(id FLOAT,name VARCHAR(5),CHECK(id>10 and id<20));

'''id值是唯一的，不能重复'''CREATE TABLE student(id INT UNIQUE,name VARCHAR(200) UNIQUE);'''有id和name值有一个存在就报错'''CREATE TABLE student(id INT,name VARCHAR(200),UNIQUE(id),UNIQUE(name));'''id、name值同时存在就报错'''CREATE TABLE student(id INT,name VARCHAR(200),UNIQUE(id,name));

'''id为主键：列级约束'''CREATE TABLE student(id INT(4) PRIMARY KEY ,name VARCHAR(200));'''id为主键：表级约束，建议'''CREATE TABLE student(id INT(4),name VARCHAR(200),PRIMARY KEY (id));'''id和id1为复合主键：当id和id1同时存在时报错'''CREATE TABLE student(id INT,id1 INT,name VARCHAR(200),PRIMARY KEY (id,id1));

'''
1、示例表：班级表、学生表、课程表、学生身份证
2、班级和学生是一对多关系
3、课程对学生是多对多关系
4、学生身份证表和学生表一对一关系，一对一可以创建成一张表，实际中用的不多
6、依赖于外键的约束：REFERENCES：关联键，用于一对多、多对多关联另一张表的键ON DELETE RESTRICTT：父表数据被删除，会阻止删除，默认就是这一项ON UPDATE RESTRICT：父表数据被修改，会阻止修改，默认就是这一项ON DELETE CASCADE：父表数据被删除，子表相关的数据会一起删除ON UPDATE CASCADE：父表数据被修改，子表也会跟着修改ON DELETE SET NULL：父表数据被删除，子表相关的数据会设置成NULLON UPDATE SET NULL：父表数据被修改，子表相关的数据会设置成NULL'''

'''1、创建班级表，设置主键为id2、创建学生表，设置主键为id，外键为class_id，并关联班级表中的id3、未添加班级数据前，不能添加学生；添加学生时，不存在的班级会报错'''CREATE TABLE classa(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));CREATE TABLE student(id INT NOT NULL AUTO_INCREMENT,class_id INT,name VARCHAR(255),PRIMARY KEY(id),FOREIGN KEY(class_id) REFERENCES classa(id));'''1、该学生表增加了外键约束：ON DELETE CASCADE、ON UPDATE CASCADE2、当班级表id变化，学生表中的class_id也会跟着变化3、当班级表中某个班级删除，学生表中与该班级相关的学生也会删除'''CREATE TABLE student(id INT NOT NULL AUTO_INCREMENT,class_id INT,name VARCHAR(255),PRIMARY KEY(id),FOREIGN KEY(class_id) REFERENCES classa(id) ON DELETE CASCADE ON UPDATE CASCADE );'''1、创建课程表2、创建课程与学生之间的中间表，分别设置外键关联学生id、课程id'''CREATE TABLE course(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));CREATE TABLE stu_course(id INT NOT NULL AUTO_INCREMENT,stu_id INT,course_id INT,PRIMARY KEY(id),FOREIGN KEY(stu_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY(course_id) REFERENCES course(id) ON DELETE CASCADE ON UPDATE CASCADE);

'''约束名字：student_key为主键的约束名字'''CREATE TABLE student(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(255),CONSTRAINT student_key PRIMARY KEY(id))'''约束名字：设置stu_key为stu_id外键名字，设置course_key为course_id外键名字'''CREATE TABLE stu_course(id INT NOT NULL AUTO_INCREMENT,stu_id INT,course_id INT,PRIMARY KEY(id),CONSTRAINT stu_key FOREIGN KEY(stu_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT course_key FOREIGN KEY(course_id) REFERENCES course(id) ON DELETE CASCADE ON UPDATE CASCADE);

'''查询约束名字'''SHOW INDEX IN stu_course;SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name='stu_course';

#删除表DROP TABLE table_name;#删除多个表DROP TABLE table_name1,table_name2;

DROP TABLE IF EXISTS table_name1,table_name2;

#清空表TRUNCATE TABLE table_name;

'''添加一列'''ALTER TABLE student ADD name VARCHAR(20);'''添加多列'''ALTER TABLE student ADD name VARCHAR(20),ADD name1 VARCHAR(20);

'''重命名name1，多个列名逗号隔开'''ALTER TABLE student RENAME COLUMN name1 TO age

'''student重命名为new_student，多个表逗号隔开'''RENAME TABLE student TO new_student

'''修改年龄为INT类型，且检验约束为0-100，多个列逗号隔开'''ALTER TABLE student MODIFY age INT CHECK (0<age<100);

'''name1修改为name，且数据类型为VARCHAR(255)，并添加不能为NULL的约束，多个列逗号隔开'''ALTER TABLE student CHANGE name1 name VARCHAR(255) NOT NULL;

'''删除列，多个列逗号隔开'''ALTER TABLE student DROP name

ALTER TABLE table_name ADD INDEX: 添加索引ALTER TABLE table_name ADD PRIMARY KEY: 添加主键ALTER TABLE table_name ADD FOREIGN KEY: 添加外键ALTER TABLE table_name ADD UNIQUE INDEX: 添加唯一索引ALTER TABLE table_name ADD CHECK: 添加检查约束ALTER TABLE table_name DROP INDEX index_name: 删除索引ALTER TABLE table_name DROP PRIMARY KEY: 删除主键ALTER TABLE table_name DROP FOREIGN KEY fk_symbol: 删除外键ALTER TABLE table_name DROP CHECK symbol: 删除检查约束ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name: 重命名索引

'''先创建班级表'''CREATE TABLE class(id INT AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));

'''需添加全部列：添加id=1、name='一年级一班'的数据 '''''INSERT INTO class VALUE(1,'一年级一班')'''1、添加部分列：添加name='一年级二班'的数据2、因id添加的约束类型自增，所以一年级二班id=2'''INSERT INTO class(name) VALUE('一年级二班')

'''1、添加部分列：添加一年级一班、一年级二班2、支持添加全部列、支持添加一条数据'''INSERT INTO class(name) VALUES('一年级三班'),('一年级四班');

'''1、因为id=4存在，则把id=4，更新为二年级一班2、多个更新值要都好隔开3、支持VALUES'''INSERT INTO class VALUE(4,'二年级一班') ON DUPLICATE KEY UPDATE name=VALUES(name);'''因为id=5不存在，则把id=5，添加到表中'''INSERT INTO class VALUE(5,'二年级二班') ON DUPLICATE KEY UPDATE name=VALUES(name);

'''1、创建一个新的班级表2、查询class表中的数据，并插入到new_class3、全部插入的时候，需要保证表结构一致4、部分插入的时候，需要保证插入的列一致'''CREATE TABLE new_class(id INT AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id));INSERT INTO new_class SELECT * FROM class;

'''1、REPLACE语句和INSERT语句很像，它们的不同之处在于，当插入过程中出现了重复的主键或者重复的唯一索引的时候，INSERT 语句会产生一个错误，而 REPLACE 语句则先删除旧的行，再插入新的行2、只示例VALUE，同时具有INSERT语句相关语法'''REPLACE INTO new_class VALUE(1,'六年级一班')

'''先创建学生表'''CREATE TABLE student(        id INT AUTO_INCREMENT,        cla_id INT NOT NULL,        name VARCHAR(255),        gender ENUM('男','女','未知') DEFAULT '未知',        age INT,        PRIMARY KEY(id),        CONSTRAINT cla_key FOREIGN KEY(cla_id) REFERENCES class(id) ON DELETE CASCADE ON UPDATE CASCADE ,        CHECK (gender IN ('男','女','未知')));                   '''给学生表插入数据'''INSERT INTO student(cla_id, name, age,gender) VALUES(1,'隗莹',5,'女'),                                                    (1,'独珠',6,'女'),                                                    (1,'时霖',7,'男'),                                                    (1,'尚菊',4,'女'),                                                    (1,'旷石',5,'男'),                                                    (1,'张一',6,'未知'),                                                    (1,'张二',6,'未知'),                                                    (1,'张三',5,'女'),                                                    (1,'张四',5,'女'),                                                    (1,'张三丰',7,'男'),                                                    (2,'罕子薇',5,'女'),                                                    (2,'诺长',6,'女'),                                                    (2,'春怜烟',6,'男'),                                                    (3,'大珠',8,'女'),                                                    (3,'庆逸思',7,'男'),                                                    (4,'李一',6,'未知'),                                                    (4,'李二',9,'未知'),                                                    (5,'李三',10,'女'),                                                    (5,'李四',8,'女'),                                                    (6,'李三丰',7,'男');                                    

#查询所有列SELECT * FROM student;#只查询学生nameSELECT name FROM student;#查询学生name、ageSELECT name,age FROM student;

'''只演示部分查询条件，其他查询条件可参考该文章第八条'''

#查询年龄大于5的学生SELECT * FROM student WHERE age>5;#查询姓氏为张的数据，%匹配多个字符SELECT * FROM student WHERE name LIKE '张%';#查询姓氏为张的数据，且只有两位字符的数据，_匹配一个字符SELECT * FROM student WHERE name LIKE '张_';#查询姓氏为张的数据，并且性别不为未知的数据SELECT * FROM student WHERE name LIKE '张%' and gender!='未知';#查询姓氏不为张的数据，并且性别不为未知的数据SELECT * FROM student WHERE name NOT LIKE '张%' and gender!='未知';'''

'''SELECT * FROM student WHERE cla_id in (SELECT id FROM class)'''子查询：查询学生表中id、age同时大于班级表中id得最大值、年龄得平均值'''SELECT * FROM student WHERE (id,age) > (SELECT MAX(id),AVG(id) FROM class);

'''返回前两行'''SELECT * FROM student LIMIT 2;'''跳过 2 个记录行，并从 第 3 个记录行开始，最多返回 5 个记录行'''SELECT * FROM student LIMIT 2,5;

'''返回性别重复数据'''SELECT DISTINCT gender FROM student;'''返回性别、年龄都不重复数据'''SELECT DISTINCT gender,age FROM student;'''全部列都不重复数据'''SELECT DISTINCT * FROM student;

'''根据age降序查询'''SELECT * FROM student WHERE age>5 ORDER BY age DESC;'''根据age升序查询'''SELECT * FROM student WHERE age>5 ORDER BY age ASC;'''多个字段排序，先根据age升序查询，再根据id降序查询'''SELECT * FROM student WHERE age>5 ORDER BY age ASC,id DESC;#自定义排序规则SELECT name FROM student WHERE name LIKE '张_' ORDER BY FIELD(name,'张四','张三','张二','张一');SELECT name FROM student WHERE name LIKE '张_'                      ORDER BY CASE name                          WHEN name='张四' THEN 4                          WHEN name='张三' THEN 3                          WHEN name='张二' THEN 2                          WHEN name='张一' THEN 1                          END;

'''性别分组'''
SELECT gender FROM student GROUP BY gender;
'''
1、单独分组没什么意义，经常和函数一起使用
2、GROUP_CONCAT(name) 把分组后的name，用逗号拼接到一起
3、COUNT(*) 分别计算分组后的数量'''SELECT gender,GROUP_CONCAT(name) AS name,COUNT(*) AS count FROM student GROUP BY gender ORDER BY count;'''HAVING：分组的条件查询'''SELECT gender,GROUP_CONCAT(name) AS name,COUNT(*) AS count FROM student GROUP BY gender HAVING count>5;'''WITH ROLLU的作用是：在最后新增一行，来记录当前列里所有记录的总和'''SELECT gender,GROUP_CONCAT(name) AS name,COUNT(*) AS count FROM student GROUP BY gender WITH ROLLUP;

'''SELECT name AS '姓名',age AS '年龄',gender AS '性别' FROM student as stu WHERE stu.age>5;

'''1、多表查询2、查询出学生所在班级的名字及学生信息'''SELECT cla.name AS '班级',       cla.id AS '班级id',       stu.id AS '学生id',       stu.name AS '学生姓名',       stu.age AS '学生年龄',       stu.gender AS '学生性别'FROM student AS stu,class AS claWHERE stu.cla_id = cla.id;

'''查询年龄大于7的学生所在的班级名字及学生信息'''SELECT cla.name AS '班级',       cla.id AS '班级id',       stu.id AS '学生id',       stu.name AS '学生姓名',       stu.age AS '学生年龄',       stu.gender AS '学生性别'FROM student AS stu CROSS JOIN class AS cla ON cla.id = stu.cla_id WHERE stu.age>7;

'''查询年龄大于7的学生所在的班级名字及学生信息'''SELECT cla.name AS '班级',       cla.id AS '班级id',       stu.id AS '学生id',       stu.name AS '学生姓名',       stu.age AS '学生年龄',       stu.gender AS '学生性别'FROM class AS cla INNER JOIN student AS stu ON cla.id = stu.cla_id WHERE stu.age>7;'''等同于'''SELECT cla.name AS '班级',       cla.id AS '班级id',       stu.id AS '学生id',       stu.name AS '学生姓名',       stu.age AS '学生年龄',       stu.gender AS '学生性别'FROM student AS stu,class AS claWHERE stu.cla_id = cla.id AND stu.age>7;

SELECT cla.name AS '班级',       cla.id AS '班级id',       stu.id AS '学生id',       stu.name AS '学生姓名',       stu.age AS '学生年龄',       stu.gender AS '学生性别'FROM class AS cla LEFT JOIN student AS stu ON cla.id = stu.cla_id;

'''同样的sql，使用RIGHT JOIN把表的位置互换实现了LEFT JOIN的效果''''SELECT cla.name AS '班级',       cla.id AS '班级id',       stu.id AS '学生id',       stu.name AS '学生姓名',       stu.age AS '学生年龄',       stu.gender AS '学生性别'FROM student AS stu RIGHT JOIN class AS cla ON cla.id = stu.cla_id;

'''创建测试学生表，并把学生表中的数据插入测试学生表'''CREATE TABLE test_student(        id INT(4) ZEROFILL AUTO_INCREMENT,        cla_id INT NOT NULL,        name VARCHAR(255),        gender ENUM('男','女','未知') DEFAULT '未知',        age INT,        PRIMARY KEY(id),        CONSTRAINT test_cla_key FOREIGN KEY(cla_id) REFERENCES class(id) ON DELETE CASCADE ON UPDATE CASCADE ,        CHECK (gender IN ('男','女','未知'))) AUTO_INCREMENT=100;

INSERT INTO test_student(name,age,cla_id,gender) SELECT name,age,cla_id,gender from student;

'''1、UNION内部的SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条 SELECT 语句中的列的顺序必须相同2、查询两张表中年龄大于7的学生'''SELECT * FROM student WHERE age>7 UNION SELECT * FROM test_student WHERE age>7;'''1、查询两张表中年龄大于7的学生2、与直接使用UNION区别在于，把联合查询的结果集创建成new_stu虚拟表，再做条件查询'''SELECT * FROM (SELECT * FROM student WHERE age>7 UNION SELECT * FROM test_student) AS new_stu WHERE age>7;

```'''把学生表中得第一个学生得名字改为张子凡'''UPDATE student SET name = '张子凡' LIMIT 1;'''把学生表中得第一个学生得名字改为张子凡，并把年龄改为20'''UPDATE student SET name = '张子凡',age=20 LIMIT 1;'''把三年级二班改为四年级一班'''UPDATE class SET name = '四年级一班' WHERE name='三年级二班';'''子查询修改：把student第一个姓李的名字修改成test_student第一个姓张的名字'''UPDATE student SET name = (SELECT name FROM test_student WHERE name LIKE '张%' LIMIT 1) WHERE name LIKE '李%' LIMIT 1;```

SELECT COUNT(*) AS '总数',       AVG(age) AS '平均年龄',       MAX(age) AS '最大年龄',       MIN(age) AS '最小年龄',       SUM(age) AS '年龄总数'FROM student;

'''查询当前日期'''SELECT NOW() AS '当前日期时间',CURDATE() AS '当前日期',CURTIME() AS '当前时间';'''给学生表添加currentDate列，并设置成当前日期'''ALTER TABLE student MODIFY currentDate DATETIME;UPDATE student SET currentDate = NOW() LIMIT 20;

SELECT DATE_FORMAT(currentDate,'%Y-%m-%d') FROM student;

SELECT ADDDATE(currentDate,2) FROM student;

SELECT ADDTIME(currentDate,3) FROM student;

SELECT DATEDIFF(NOW(),currentDate) FROM student;

'''
1、日期加上两天
2、type：MICROSECOND（微秒）、SECOND（秒）、MINUTE（分）、HOUR（小时）、DAY（天）、WEEK（周）、MONTH（月）'''SELECT DATE_ADD(currentDate,INTERVAL 2 DAY) FROM student;'''日期减两天'''SELECT DATE_SUB(currentDate,INTERVAL 2 DAY) FROM student;