CREATE DATABASE IF NOT EXISTS cms DEFAULT CHARACTER SET utf8;USE cms;-- 管理员表cms_adminCREATE TABLE cms_admin(id TINYINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,password CHAR(32) NOT NULL,email VARCHAR(50) NOT NULL DEFAULT 'admin@qq.com',role ENUM('普通管理员','超级管理员') DEFAULT '普通管理员');INSERT cms_admin(username,password,email,role) VALUES('admin','admin','admin@qq.com',2);INSERT cms_admin(username,password) VALUES('king','king'),('麦子','maizi'),('queen','queen'),('test','test');-- 创建分类表cms_cateCREATE TABLE cms_cate(id TINYINT UNSIGNED AUTO_INCREMENT KEY,cateName VARCHAR(50) NOT NULL UNIQUE,cateDesc VARCHAR(200) NOT NULL DEFAULT '');INSERT cms_cate(cateName,cateDesc) VALUES('国内新闻','聚焦当今最热的国内新闻'),('国际新闻','聚焦当今最热的国际新闻'),('体育新闻','聚焦当今最热的体育新闻'),('军事新闻','聚焦当今最热的军事新闻'),('教育新闻','聚焦当今最热的教育新闻');-- 创建新闻表cms_newsCREATE TABLE cms_news(id INT UNSIGNED AUTO_INCREMENT KEY,title VARCHAR(50) NOT NULL UNIQUE,content TEXT,clickNum INT UNSIGNED DEFAULT 0,pubTime INT UNSIGNED,cId TINYINT UNSIGNED NOT NULL COMMENT '新闻所属分类，对应分类表中的id',aId TINYINT UNSIGNED NOT NULL COMMENT '哪个管理员发布的，对应管理员表中的id');INSERT cms_news(title,content,pubTime,cId,aId) VALUES('亚航客机失联搜救尚无线索 未发求救信号','马来西亚亚洲航空公司一架搭载155名乘客的客机28日早晨从印度尼西亚飞往新加坡途中与空中交通控制塔台失去联系，下落不明。',1419818808,1,2),('北京新开通四条地铁线路 迎接首位客人','12月28日凌晨，随着北京地铁6号线二期、7号线、15号线西段、14号线东段的开通试运营，北京的轨道交通运营里程将再添62公里，共计达到527公里。当日凌晨5时许，北京地铁7号线瓷器口换乘站迎来新线开通的第一位乘客。',1419818108,2,1),('考研政治题多次出现***讲话内容','新京报讯 （记者许路阳 (微博)）APEC反腐宣言、国家公祭日、依法治国……昨日，全国硕士研究生招生考试进行首日初试，其中，思想政治理论考题多次提及时事热点，并且多次出现***在不同场合的讲话内容。',1419818208,3,2),('深度-曾雪麟：佩兰别重蹈卡马乔覆辙','12月25日是前国足主帅曾雪麟的85岁大寿，恰逢圣诞节，患有尿毒症老爷子带着圣诞帽度过了自己的生日。此前，腾讯记者曾专访曾雪麟，尽管已经退休多年，但老爷子仍旧关心着中国足球，为国足揪心，对于国足近几位的教练，他只欣赏高洪波。对即将征战亚洲杯的国足，老爷子希望佩兰不要重蹈卡马乔的覆辙',1419818308,2,4),('国产JAD-1手枪枪架投入使用 手枪可变"冲锋枪"','日前，JAD-1型多功能手枪枪架通过公安部特种警用装备质量监督检验中心检验，正式投入生产使用。此款多功能枪架由京安盾(北京)警用装备有限公司开发研制，期间经广东省江门市公安特警支队试用，获得好评。',1419818408,4,4),('麦子学院荣获新浪教育大奖','麦子学院最大的职业IT教育平台，获奖了',1419818508,1,5),('麦子学院荣获腾讯教育大奖','麦子学院最大的职业IT教育平台，获奖了',1419818608,1,5),('麦子学院新课上线','麦子学院PHP课程马上上线了，小伙伴快来报名学习哈',1419818708,1,5);-- 创建身份表 provincesCREATE TABLE provinces(id TINYINT UNSIGNED AUTO_INCREMENT KEY,proName VARCHAR(10) NOT NULL UNIQUE);INSERT provinces(proName) VALUES('北京'),('上海'),('深圳'),('广州'),('重庆');-- 创建用户表cms_userCREATE TABLE cms_user(id INT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,password CHAR(32) NOT NULL,email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',regTime INT UNSIGNED NOT NULL,face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',proId TINYINT UNSIGNED NOT NULL COMMENT '用户所属省份');INSERT cms_user(username,password,regTime,proId)VALUES('张三','zhangsan',1419811708,1),('张三丰','zhangsanfeng',1419812708,2),('章子怡','zhangsan',1419813708,3),('long','long',1419814708,4),('ring','ring',1419815708,2),('queen','queen',1419861708,3),('king','king',1419817708,5),('blek','blek',1419818708,1),('rose','rose',1419821708,2),('lily','lily',1419831708,2),('john','john',1419841708,2);-- 查询SELECT * FROM cms_admin;SELECT cms_admin.* FROM cms_admin;-- 查询管理员编号和名称SELECT id,username FROM cms_admin;SELECT username,id,role FROM cms_admin;-- 表来自于哪个数据库下db_name.tbl_nameSELECT id,username,role FROM cms.cms_admin;-- 字段来自于哪张表SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;-- 给表名起别名SELECT id,username FROM cms_admin AS a;SELECT id,username FROM cms_admin a;SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;-- 给字段起别名SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a; SELECT id AS proId,proId AS id,username FROM cms_user;SELECT 1,2,3,4,5,id,username FROM cms_user;-- WHERE条件-- 查询编号为1的用户SELECT id,username,email FROM cms_user WHERE id=1;SELECT id,username,email FROM cms_user WHERE username='king';-- 查询编号不为1的用户SELECT  * FROM cms_user WHERE id!=1;SELECT  * FROM cms_user WHERE id<>1;-- 添加age字段ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;INSERT cms_user(username,password,regTime,proId,age)VALUES('test1','test1',1419811708,1,NULL);-- 查询表中记录age值为NULLSELECT * FROM cms_user WHERE age=NULL;SELECT * FROM cms_user WHERE age<=>NULL;SELECT * FROM cms_user WHERE age<=>18;-- IS NULL 或者IS NOT NULLSELECT * FROM cms_user WHERE age IS NULL;-- 查询编号在3~10之间的用户SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;-- 查询编号为1，3，5，7，9，11，13，100SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);-- 查询proId为1 和3的用户SELECT * FROM cms_user WHERE proId IN(1,3);-- 查询用户名为king,queen,张三，章子怡的记录SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','张三','章子怡');-- 模糊查询-- %:代表0个一个或者多个任意字符-- _:代表1个任意字符-- 查询姓张的用户SELECT * FROM cms_user WHERE username LIKE '张%';-- 查询用户名中包含in的用户SELECT * FROM cms_user WHERE username LIKE '%in%';SELECT * FROM cms_user WHERE username LIKE '%';-- 查询用户名为3位的用户SELECT * FROM cms_user WHERE username LIKE '___';--用户名_i%SELECT * FROM cms_user WHERE username LIKE '_I%';SELECT * FROM cms_user WHERE username LIKE 'king';SELECT * FROM cms_user WHERE username NOT LIKE '_I%';-- 查询用户名为king并且密码为king的用户SELECT * FROM cms_user WHERE username='king' AND password='king';-- 查询编号大于等于3的变量年龄不为NULL的用户SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;-- 查询编号大于等于3的变量年龄不为NULL的用户 并且proId为的3SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;-- 查询编号在5~10的用户并且用户名为4位的用户SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';-- 查询用户名以张开始或者用户所在身份为2,4的记录SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);-- 按照用户所属身份分组proIdSELECT * FROM cms_user GROUP BY proId;-- 向用户表中添加性别字段ALTER TABLE cms_user ADD sex ENUM('男','女','保密');UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);UPDATE cms_user SET sex='保密' WHERE id IN(12,11);--按照用户性别分组SELECT * FROM cms_user GROUP BY sex;--按照字段位置分组SELECT * FROM cms_user GROUP BY 7;--按照多个字段分组SELECT * FROM cms_user GROUP BY sex,proId;-- 查询编号大于等于5的用户按照sex分组SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;-- 查询id,sex,用户名详情按照性别分组SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;--查询proId，性别详情，注册时间详情，用户名详情 安装proIdSELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)FROM cms_user GROUP BY proId;UPDATE cms_user SET age=11 WHERE id=1;UPDATE cms_user SET age=21 WHERE id=2;UPDATE cms_user SET age=33 WHERE id=3;UPDATE cms_user SET age=44 WHERE id=4;UPDATE cms_user SET age=25 WHERE id=5;UPDATE cms_user SET age=77 WHERE id=6;UPDATE cms_user SET age=56 WHERE id=7;UPDATE cms_user SET age=88 WHERE id=8;UPDATE cms_user SET age=12 WHERE id=9;UPDATE cms_user SET age=32 WHERE id=10;UPDATE cms_user SET age=65 WHERE id=11;--查询编号,sex,用户名详情以及组中总人数按照sex分组SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;-- 统计表中所有记录SELECT COUNT(*) AS totalUsers FROM cms_user;SELECT COUNT(id) AS totalUsers FROM cms_user;--COUNT(字段)不统计NULL值SELECT COUNT(age) AS totalUsers FROM cms_user;--查询编号,性别,用户名详情，组中总人数，组中最大年龄，最小年龄，-- 平均年龄，以及年龄总和按照性别分组SELECT id,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_age,AVG(age) AS avg_age,SUM(age) AS sum_ageFROM cms_userGROUP BY sex;-- WITH ROLLUPSELECT id,sex,COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_ageFROM cms_userGROUP BY sex WITH ROLLUP;SELECT id,sex,COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_age,SUM(age) AS sum_ageFROM cms_userGROUP BY sex WITH ROLLUP;--查询性别sex,用户名详情,组中总人数，最大年龄，年龄总和,根据性别分组SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user GROUP BY sex;-- 查询组中人数大于2的SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user GROUP BY sexHAVING COUNT(*)>2;-- 查询组中人数大于2并且最大年龄大于60的SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user GROUP BY sexHAVING COUNT(*)>2 AND MAX(age)>60;-- 查询编号大于等于2的用户SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user WHERE id>=2GROUP BY sexHAVING COUNT(*)>2 AND MAX(age)>60;SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user WHERE id>=2HAVING COUNT(*)>2 AND MAX(age)>60;-- 按照id降序排列DESC 默认的是ASCSELECT * FROM cms_user ORDER BY id ;SELECT * FROM cms_user ORDER BY id ASC;SELECT * FROM cms_user ORDER BY id DESC;-- 按照年龄升序排列SELECT * FROM cms_user ORDER BY age ASC;SELECT * FROM cms_user ORDER BY 1 DESC;UPDATE cms_user SET age=12 WHERE id=5;-- 按照年龄升序，id降序排列SELECT * FROM cms_user ORDER BY age ASC,id DESC;SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age FROM cms_user WHERE id>=2 GROUP BY sexHAVING COUNT(*)>=2ORDER BY age DESC,id ASC;-- 实现记录随机SELECT * FROM cms_user ORDER BY RAND();-- 查询表中前3条记录SELECT * FROM cms_user LIMIT 3;SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;-- 查询表中前一条记录SELECT * FROM cms_user LIMIT 1;SELECT * FROM cms_user LIMIT 0,1;SELECT * FROM cms_user LIMIT 1,1;SELECT * FROM cms_user LIMIT 0,5;SELECT id,sex,age,GROUP_CONCAT(username),COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_age,AVG(age) AS avg_age,SUM(age) AS sum_ageFROM cms_userWHERE id>=1GROUP BY sexHAVING COUNT(*)>=2ORDER BY age DESCLIMIT 0,2;

-- 创建普通索引CREATE TABLE test4(id TINYINT UNSIGNED,username VARCHAR(20),INDEX in_id(id),KEY in_username(username));DROP INDEX in_id ON test4;DROP INDEX in_username ON test4;CREATE INDEX in_id ON test4(id);ALTER TABLE test4 ADD INDEX in_username(username);-- 创建唯一索引CREATE TABLE test5(id TINYINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,card CHAR(18) NOT NULL,UNIQUE KEY uni_card(card));ALTER TABLE test5 DROP INDEX uni_card;DROP INDEX username ON test5;CREATE UNIQUE INDEX uni_username ON test5(username);ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);-- 创建全文索引CREATE TABLE test6(id TINYINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,userDesc VARCHAR(20) NOT NULL,FULLTEXT INDEX full_userDesc(userDesc));DROP INDEX full_userDesc ON test6;CREATE FULLTEXT INDEX full_userDesc ON test6(userDesc);-- 创建单列索引CREATE TABLE test7(id TINYINT UNSIGNED AUTO_INCREMENT KEY,test1 VARCHAR(20) NOT NULL,test2 VARCHAR(20) NOT NULL,test3 VARCHAR(20) NOT NULL,test4 VARCHAR(20) NOT NULL,INDEX in_test1(test1));-- 创建多列索引CREATE TABLE test8(id TINYINT UNSIGNED AUTO_INCREMENT KEY,test1 VARCHAR(20) NOT NULL,test2 VARCHAR(20) NOT NULL,test3 VARCHAR(20) NOT NULL,test4 VARCHAR(20) NOT NULL,INDEX mul_t1_t2_t3(test1,test2,test3));ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);CREATE TABLE test9(id TINYINT UNSIGNED AUTO_INCREMENT KEY,test1 VARCHAR(20) NOT NULL,test2 VARCHAR(20) NOT NULL,test3 VARCHAR(20) NOT NULL,test4 VARCHAR(20) NOT NULL,UNIQUE KEY mul_t1_t2_t3(test1,test2,test3));-- 创建空间索引CREATE TABLE test10(id TINYINT UNSIGNED AUTO_INCREMENT KEY,test GEOMETRY NOT NULL,SPATIAL INDEX spa_test(test))ENGINE=MyISAM;DROP INDEX spa_test ON test10;CREATE SPATIAL INDEX spa_test ON test10(test);

# 注释内容-- 注释内容-- 创建maizi数据库CREATE DATABASE IF NOT EXISTS `maizi` DEFAULT CHARACTER SET 'UTF8';USE `maizi`;-- 创建学员表(user)-- 编号 id-- 用户名 username-- 年龄 age-- 性别 sex-- 邮箱 email-- 地址 addr-- 生日 birth-- 薪水 salary-- 电话 tel-- 是否结婚 married-- 注意：当需要输入中文的时候，需要临时转换客户端的编码方式-- SET NAMES GBK;-- 字段注释 通过COMMENT 注释内容 给字段添加注释CREATE TABLE IF NOT EXISTS `user`(id SMALLINT,username VARCHAR(20),age TINYINT,sex ENUM('男','女','保密'),email VARCHAR(50),addr VARCHAR(200),birth YEAR,salary FLOAT(8,2),tel INT,married TINYINT(1) COMMENT '0代表未结婚，非0代表已婚')ENGINE=INNODB CHARSET=UTF8;-- 创建课程表 course-- 编号 cid-- 课程名称courseName-- 课程描述courseDescCREATE TABLE IF NOT EXISTS course(cid TINYINT,courseName VARCHAR(50),courseDesc VARCHAR(200));-- 创建新闻分类表cms_cate-- 编号、分类名称、分类描述CREATE TABLE IF NOT EXISTS cms_cate(id TINYINT,cateName VARCHAR(50),cateDesc VARCHAR(200))ENGINE=MyISAM CHARSET=UTF8;-- 创建新闻表 cms_news-- 编号、新闻标题、新闻内容、新闻发布时间、点击量、是否置顶CREATE TABLE IF NOT EXISTS cms_news(id INT,title VARCHAR(50),content TEXT,pubTime INT,clickNum INT,isTop TINYINT(1) COMMENT '0代表不置顶，1代表置顶');-- 查看cms_news表的表结构DESC cms_news;DESCRIBE cms_news;SHOW COLUMNS FROM cms_news;-- 测试整型CREATE TABLE test1(num1 TINYINT,num2 SMALLINT,num3 MEDIUMINT,num4 INT,num5 BIGINT);-- 向表中插入记录INSERT tbl_name VALUE|VALUES(值,...);INSERT test1 VALUES(-128,-32768,-8388608,-2147483648,-9223372036854775808);INSERT test1 VALUES(-129,-32768,-8388608,-2147483648,-9223372036854775808);-- 查询表中所有记录SELECT * FROM tbl_name;SELECT * FROM test1;-- 无符号UNSIGNEDCREATE TABLE test2(num1 TINYINT UNSIGNED,num2 TINYINT );INSERT test2 VALUES(0,-12);-- 零填充ZEROFILLCREATE TABLE test3(num1 TINYINT ZEROFILL,num2 SMALLINT ZEROFILL,num3 MEDIUMINT ZEROFILL,num4 INT ZEROFILL,num5 BIGINT ZEROFILL);INSERT test3 VALUES(1,1,1,1,1);INSERT test3 VALUES(123,1,1,1,1);-- 测试浮点类型CREATE TABLE test4(num1 FLOAT(6,2),num2 DOUBLE(6,2),num3 DECIMAL(6,2));INSERT test4 VALUES(3.1415,3.1415,3.1415);INSERT test4 VALUES(3.1495,3.1495,3.1495);-- 测试CHAR和VARCHARCREATE TABLE IF NOT EXISTS test5(str1 CHAR(5),str2 VARCHAR(5));INSERT test5 VALUES('1','1');INSERT test5 VALUES('12345','12345');INSERT test5 VALUES('123456','123456');INSERT test5 VALUES('','');INSERT test5 VALUES('1  ','1  ');INSERT test5 VALUES('  a','  a');INSERT test5 VALUES('啊啊啊啊啊','麦子学院好');CREATE TABLE test6(str1 TEXT);INSERT test6 VALUES('skdfjlksdfjlksjdflkj塑料口袋精灵是看见对方离开首都基辅绿卡时间的联发科技');-- 测试枚举类型CREATE TABLE IF NOT EXISTS test7(sex ENUM('男','女','保密    '));INSERT test7 VALUES('男     ');INSERT test7 VALUES('女     ');INSERT test7 VALUES('保密');INSERT test7 VALUES('保密1');INSERT test7 VALUES(2);INSERT test7 VALUES(0);INSERT test7 VALUES(NULL);INSERT test7 VALUES('');-- 测试集合类型CREATE TABLE IF NOT EXISTS test8(fav SET('A','B','C','D'));INSERT test8 VALUES('A,C,D');INSERT test8 VALUES('D,B,A');INSERT test8 VALUES(3);INSERT test8 VALUES(15);-- 测试日期时间CREATE TABLE IF NOT EXISTS test9(birth YEAR);INSERT test9 VALUES(1901);INSERT test9 VALUES(2155);INSERT test9 VALUES(2156);CREATE TABLE IF NOT EXISTS test10(test TIME);INSERT test10 VALUES('1 12:12:12');INSERT test10 VALUES('11:11');CREATE TABLE IF NOT EXISTS test11(test DATE);INSERT test11 VALUES('12-6-7');

-- 测试主键CREATE TABLE IF NOT EXISTS user1(id INT PRIMARY KEY,username VARCHAR(20));-- 查看创建表的标的定义SHOW CREATE TABLE user1;INSERT user1 VALUES(1,'king');INSERT user1 VALUES(13,'QUEEN');CREATE TABLE IF NOT EXISTS user2(id INT,username VARCHAR(20),card CHAR(18),PRIMARY KEY(id,card));INSERT user2 VALUES(1,'king','111');INSERT user2 VALUES(1,'queen','112');CREATE TABLE IF NOT EXISTS user3(id INT KEY,username VARCHAR(20));CREATE TABLE IF NOT EXISTS user4(id INT,username VARCHAR(20) KEY);-- 测试AUTO_INCREMENTCREATE TABLE IF NOT EXISTS user5(id SMALLINT KEY AUTO_INCREMENT,username VARCHAR(20));INSERT user5 VALUES(1,'KING');INSERT user5(username) VALUES('queen1');INSERT user5 VALUES(111,'KING1');INSERT user5 VALUES(NULL,'AAAA');INSERT user5 VALUES(DEFAULT,'AAAA');CREATE TABLE IF NOT EXISTS user6(id SMALLINT KEY AUTO_INCREMENT,username VARCHAR(20))AUTO_INCREMENT=100;INSERT user6(username) VALUES('queen1');-- 修改自增长的值ALTER TABLE user6 AUTO_INCREMENT =500;-- 测试NOT NULLCREATE TABLE IF NOT EXISTS user7(id INT UNSIGNED KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,password CHAR(32) NOT NULL,age TINYINT UNSIGNED);INSERT user7(username,password) VALUES('KING','KING');INSERT user7(username,password,age) VALUES('KING1','KING1',12);INSERT user7(username,password) VALUES(NULL,NULL);-- 测试DEFAULTCREATE TABLE IF NOT EXISTS user8(id INT UNSIGNED KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,password CHAR(32) NOT NULL,age TINYINT UNSIGNED DEFAULT 18,addr VARCHAR(50) NOT NULL DEFAULT '北京',sex ENUM('男','女','保密') NOT NULL DEFAULT '男');INSERT user8(username,password) VALUES('KING','KING');INSERT user8 VALUES(2,'QUEEN','QUEEN',29,'上海','保密');INSERT user8 VALUES(3,'QUEEN','QUEEN',DEFAULT,DEFAULT,'保密');-- 测试UNIQUE KEYCREATE TABLE IF NOT EXISTS user9(id TINYINT UNSIGNED KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL UNIQUE,card CHAR(18) UNIQUE);INSERT user9(username) VALUES('A');INSERT user9(username,card) VALUES('B','111');INSERT user9(username,card) VALUES('B1',NULL);INSERT user9(username,card) VALUES('B2',NULL);--CREATE TABLE [IF NOT EXISTS] tbl_name(--字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY| UNIQUE [KEY]] [AUTO_INCREMENT]--)ENGINE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;-- 创建用户表CREATE TABLE IF NOT EXISTS user10(id SMALLINT UNSIGNED KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL UNIQUE,password CHAR(32) NOT NULL,email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',age TINYINT UNSIGNED DEFAULT 18,sex ENUM('男','女','保密') DEFAULT '保密',addr VARCHAR(200) NOT NULL DEFAULT '北京',salary FLOAT(6,2),regTime INT UNSIGNED,face CHAR(100) NOT NULL DEFAULT 'default.jpg');-- 将user10重命名成user11ALTER TABLE user10 RENAME TO user11;ALTER TABLE user11 RENAME AS user10;ALTER TABLE user10 RENAME user11;RENAME TABLE user11 TO user10;-- 添加card 字段 CHAR(18) ALTER TABLE user10 ADD card CHAR(18);ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE;ALTER TABLE user10 ADD test2 VARCHAR(20) NOT NULL FIRST;ALTER TABLE user10 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;-- 选中一次表，完成多个操作ALTER TABLE user10 ADD test4 INT NOT NULL DEFAULT 123 AFTER password,ADD test5 FLOAT(6,2) FIRST,ADD test6 SET('A','B','C');-- 删除test6字段ALTER TABLE user10 DROP test6;-- 一次删除test2,test3,test4,test5ALTER TABLE user10 DROP test2,DROP test3,DROP test4,DROP test5;-- 添加test字段删除addr字段ALTER TABLE user10 ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,DROP addr;-- 将email VARCHAR(200) ALTER TABLE user10 MODIFY email VARCHAR(200);ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com';-- 将card字段移动到test字段之后ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;-- 将test字段修改为CHAR(32) NOT NULL DEFAULT '123' 移动到第一个位置ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;-- 将test字段改为test1ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;ALTER TABLE user10 CHANGE test test INT;CREATE TABLE IF NOT EXISTS user11(id TINYINT UNSIGNED KEY AUTO_INCREMENT ,username VARCHAR(20) NOT NULL UNIQUE,age TINYINT UNSIGNED);ALTER TABLE user11 ALTER age SET DEFAULT 18;ALTER TABLE user11 ADD email VARCHAR(50) ;-- 删除默认值ALTER TABLE user11 ALTER age DROP DEFAULT;ALTER TABLE user11 ALTER email DROP DEFAULT;-- 添加主键CREATE TABLE IF NOT EXISTS test12(id INT);ALTER TABLE test12 ADD PRIMARY KEY(id);CREATE TABLE IF NOT EXISTS test13(id INT,card CHAR(18),username VARCHAR(20) NOT NULL);ALTER TABLE test13 ADD PRIMARY KEY(id,card);-- 删除test12表的主键ALTER TABLE test12 DROP PRIMARY KEY;ALTER TABLE test13 DROP PRIMARY KEY;ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);CREATE TABLE IF NOT EXISTS test14(id INT UNSIGNED KEY AUTO_INCREMENT);ALTER TABLE test14 MODIFY id INT UNSIGNED;ALTER TABLE test14 DROP PRIMARY KEY ;-- 测试添加唯一索引CREATE TABLE IF NOT EXISTS user12(id TINYINT UNSIGNED KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,card CHAR(18) NOT NULL,test VARCHAR(20) NOT NULL,test1 CHAR(32) NOT NULL);ALTER TABLE user12 ADD UNIQUE(username);ALTER TABLE user12 ADD CONSTRAINT symbol  UNIQUE KEY uni_card(card);ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);--删除唯一索引ALTER TABLE user12 DROP INDEX username;ALTER TABLE user12 DROP KEY uni_card;ALTER TABLE user12 DROP KEY mulUni_test_test1;-- 修改表的存储引擎为MyISAMALTER TABLE user12 ENGINE=MyISAM;-- 修改自增长值ALTER TABLE user12 AUTO_INCREMENT=100;-- 删除数据表DROP TABLE user12;DROP TABLE IF EXISTS user12;DROP TABLE IF EXISTS user11,user10,user9;DROP TABLE IF EXISTS user123,user8,user7,user6;

-- 测试插入记录INSERTCREATE TABLE IF NOT EXISTS user(id TINYINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,password CHAR(32) NOT NULL,email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',age TINYINT UNSIGNED DEFAULT 18);INSERT INTO user VALUES(1,'KING','KING','KING@QQ.COM',20);INSERT user VALUE(2,'QUEEN','QUEEN','QUEEN@QQ.COM',30);INSERT user(username,password) VALUES('A','AAA');INSERT user(password,username) VALUES('BBB','B');INSERT user(username,id,password,email,age) VALUES('C',55,'CCC','CCC@QQ.COM',DEFAULT);-- 一次插入多条记录INSERT user VALUES(6,'D','DDD','D@QQ.COM',35),(8,'E','EEE','E@QQ.COM',9),(18,'F','FFF','F@QQ.COM',32);-- 通过INSERT SET形式插入记录INSERT INTO user SET id=98,username='test',password='this is a test',email='123@qq.com',age=48;INSERT user SET username='maizi',password='maizixueyuan' ;CREATE TABLE IF NOT EXISTS testUser(id TINYINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE);-- 将查询结果插入到表中INSERT testUser SELECT id,username FROM user;-- 字段数目不匹配INSERT testUser SELECT * FROM user;INSERT testUser(username) SELECT username FROM user;-- 将用户表中所有的用户年龄更新15UPDATE user SET age=5;UPDATE user SET age=20,email='test@qq.com';-- 将第一个记录的password，email，ageUPDATE user SET password='king123',email='123@qq.com',age=99WHERE id=1;UPDATE user SET age=age-5 WHERE id>=3;UPDATE user SET age=DEFAULT WHERE username='A';-- 删除testUser表中的记录DELETE FROM testUser ;-- 删除user表中id为1的用户DELETE FROM user WHERE id=1;-- 彻底清空user表TRUNCATE TABLE user;

-- 更新用户名为4位的用户，让其已有年龄-3UPDATE cms_user SET age=age-3 WHERE username LIKE '____';-- 更新前3条记录，让已有年龄+10UPDATE cms_user SET age=age+10 LIMIT 3;UPDATE cms_user SET age=age+10 LIMIT 0,3;-- 按照id降序排列，更新前3条UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;-- 删除用户性别为男的用户，按照年龄降序排列，删除1前一条记录DELETE FROM cms_user WHERE sex='男' ORDER BY age DESC LIMIT 1;-- 查询cms_user id,username-- provinces,proNameSELECT cms_user.id,username,proName FROM cms_user,provinces;-- cms_user的proId对应省份表中的idSELECT cms_user.id,username,proName FROM cms_user,provincesWHERE cms_user.proId=provinces.id;-- 查询cms_user表中id,username,email,sex-- 查询provinces表proNameSELECT u.id,u.username,u.email,u.sex,p.proNameFROM cms_user AS uINNER JOIN provinces AS pON u.proId=p.id;SELECT u.id,u.username,u.email,u.sex,p.proNameFROM provinces AS pCROSS JOIN cms_user AS uON u.proId=p.id;SELECT u.id,u.username,u.email,u.sex,p.proNameFROM provinces AS pJOIN cms_user AS uON u.proId=p.id;-- 查询cms_user id,username,sex-- 查询provinces proName-- 条件是cms_user的性别为男的用户SELECT u.id,u.username,u.sex,p.proName FROM cms_user AS uJOINprovinces AS pON u.proId=p.idWHERE u.sex='男';-- 根据proName分组SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)FROM cms_user AS uJOINprovinces AS pON u.proId=p.idWHERE u.sex='男'GROUP BY p.proName;-- 对分组结果进行筛选,选出组中人数>=1的SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)FROM cms_user AS uJOINprovinces AS pON u.proId=p.idWHERE u.sex='男'GROUP BY p.proNameHAVING COUNT(*)>=1;--　　按照id升序排列SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)FROM cms_user AS uJOINprovinces AS pON u.proId=p.idWHERE u.sex='男'GROUP BY p.proNameHAVING COUNT(*)>=1ORDER BY u.id ASC;-- 限制显示条数 前2条SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)FROM cms_user AS uJOINprovinces AS pON u.proId=p.idWHERE u.sex='男'GROUP BY p.proNameHAVING COUNT(*)>=1ORDER BY u.id ASCLIMIT 0,2;--　查询cms_news中的id,title,--  查询cms_cate 中的cateNameSELECT n.id,n.title,c.cateName FROM cms_news AS nJOIN cms_cate AS cON n.cId=c.id;-- cms_news id,title-- cms_admin username,roleSELECT n.id,n.title,a.username,a.roleFROM cms_news AS nJOINcms_admin AS aON n.aId=a.id;-- cms_news id ,title-- cms_cate cateName-- cms_admin username,roleSELECT n.id,n.title,c.cateName,a.username,a.roleFROM cms_cate AS cJOIN cms_news AS nON n.cId=c.idJOIN cms_admin AS aON n.aId=a.id;-- 插入错误的数据INSERT cms_user(username,password,regTime,proId)VALUES('TEST2','TEST2','1381203974',20);-- 左外连接SELECT u.id,u.username,u.email,u.sex,p.proNameFROM cms_user AS uLEFT JOIN provinces AS pON u.proId=p.id;SELECT u.id,u.username,u.email,u.sex,p.proNameFROM provinces AS pLEFT JOIN cms_user AS uON u.proId=p.id;SELECT u.id,u.username,u.email,u.sex,p.proNameFROM provinces AS pRIGHT JOIN cms_user AS uON u.proId=p.id;SELECT u.id,u.username,u.email,u.sex,p.proNameFROM provinces AS pRIGHT JOIN cms_user AS uON u.proId=p.id;-- 创建部门表department(主表)-- id depName CREATE TABLE IF NOT EXISTS department(id TINYINT UNSIGNED AUTO_INCREMENT KEY,depName VARCHAR(20) NOT NULL UNIQUE)ENGINE=INNODB;INSERT department(depName) VALUES('教学部'),('市场部'),('运营部'),('督导部');-- 创建员工表employee(子表)-- id ,username ,depIdCREATE TABLE IF NOT EXISTS employee(id SMALLINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED)ENGINE=INNODB;INSERT employee(username,depId) VALUES('king',1),('queen',2),('张三',3),('李四',4),('王五',1);SELECT e.id,e.username,d.depName FROMemployee AS eJOINdepartment AS dON e.depId=d.id;-- 删除督导部DELETE FROM department WHERE depName='督导部';-- 创建部门表department(主表)-- id depName CREATE TABLE IF NOT EXISTS department(id TINYINT UNSIGNED AUTO_INCREMENT KEY,depName VARCHAR(20) NOT NULL UNIQUE)ENGINE=INNODB;INSERT department(depName) VALUES('教学部'),('市场部'),('运营部'),('督导部');-- 创建员工表employee(子表)-- id ,username ,depIdCREATE TABLE IF NOT EXISTS employee(id SMALLINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED,FOREIGN KEY(depId) REFERENCES department(id))ENGINE=INNODB;INSERT employee(username,depId) VALUES('king',1),('queen',2),('张三',3),('李四',4),('王五',1);-- 删除主表中的记录DELETE FROM department WHERE id=1;-- 删除employee中的属于1部门的人DELETE FROM employee WHERE depId=1;INSERT employee(username,depId) VALUES('test',11);-- 删除员工表DROP TABLE employee;CREATE TABLE IF NOT EXISTS employee(id SMALLINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED,CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id))ENGINE=INNODB;INSERT employee(username,depId) VALUES('king',3),('queen',2),('张三',3),('李四',4),('王五',2);-- 删除外键ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;-- 添加外键ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);----------------CREATE TABLE IF NOT EXISTS department(id TINYINT UNSIGNED AUTO_INCREMENT KEY,depName VARCHAR(20) NOT NULL UNIQUE)ENGINE=INNODB;INSERT department(depName) VALUES('教学部'),('市场部'),('运营部'),('督导部');-- 创建员工表employee(子表)-- id ,username ,depIdCREATE TABLE IF NOT EXISTS employee(id SMALLINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED,FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE)ENGINE=INNODB;INSERT employee(username,depId) VALUES('king',1),('queen',2),('张三',3),('李四',4),('王五',1);-- 删除部门表中的第一个部门DELETE FROM department WHERE id=1;UPDATE department SET id=id+10;--------------------CREATE TABLE IF NOT EXISTS department(id TINYINT UNSIGNED AUTO_INCREMENT KEY,depName VARCHAR(20) NOT NULL UNIQUE)ENGINE=INNODB;INSERT department(depName) VALUES('教学部'),('市场部'),('运营部'),('督导部');-- 创建员工表employee(子表)-- id ,username ,depIdCREATE TABLE IF NOT EXISTS employee(id SMALLINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED,FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL)ENGINE=INNODB;INSERT employee(username,depId) VALUES('king',1),('queen',2),('张三',3),('李四',4),('王五',1);-- 联合查询SELECT username FROM employee UNION SELECT username FROM cms_user;SELECT username FROM employee UNION ALL SELECT username FROM cms_user;SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user;-- 由[NOT] IN引发的子查询SELECT id FROM department;SELECT id,username FROM employee WHERE depId IN(1,2,3,4);SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);SELECT id,username FROM employee WHERE depId NOT IN(SELECT id FROM department);INSERT employee(username,depId) VALUES('testtest',8);--　创建学员表student-- id username scoreCREATE TABLE IF NOT EXISTS student(id TINYINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20)  NOT NULL UNIQUE,score TINYINT UNSIGNED);INSERT student(username,score) VALUES('king',95),('king1',35),('king2',45),('king3',55),('king4',65),('king5',75),('king6',80),('king7',90),('king8',25);-- 创建奖学金scholarship-- id ,levelCREATE TABLE IF NOT EXISTS scholarship(id TINYINT UNSIGNED AUTO_INCREMENT KEY,level TINYINT UNSIGNED);INSERT scholarship(level) VALUES(90),(80),(70);-- 查询获得1等奖学金的学员有SELECT level FROM scholarship WHERE id=1;SELECT id,username FROM student WHERE score>=90;SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);-- 查询部门表中SELECT * FROM department WHERE id=5;SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=4);SELECT id,username FROM employee WHERE NOT EXISTS(SELECT * FROM department WHERE id=41);-- 查询所有获得奖学金的学员SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);SELECT id,username,score FROM student WHERE score>=SOME(SELECT level FROM scholarship);-- 查询所有学员中获得一等奖学金的学员SELECT id,username,score FROM student WHERE score >=ALL(SELECT level FROM scholarship);-- 查询学员表中没有获得奖学金的学员SELECT id,username,score FROM student WHERE score
    
      ALL(SELECT level FROM scholarship);CREATE TABLE test1 (id TINYINT UNSIGNED AUTO_INCREMENT KEY,num TINYINT UNSIGNED);INSERT test1(id,num) SELECT id,score FROM student;CREATE TABLE test2 (id TINYINT UNSIGNED AUTO_INCREMENT KEY,num TINYINT UNSIGNED)SELECT id,score FROM student;CREATE TABLE test3 (id TINYINT UNSIGNED AUTO_INCREMENT KEY,score TINYINT UNSIGNED)SELECT id,score FROM student;-- ^匹配字符开始的部分-- 查询用户名以t开始的用户SELECT * FROM cms_user WHERE username REGEXP '^t';-- $匹配字符串结尾的部分SELECT * FROM cms_user WHERE username REGEXP 'g$';-- .代表任意字符SELECT * FROM cms_user WHERE username REGEXP '.';SELECT * FROM cms_user WHERE username REGEXP 'r..g';SELECT * FROM cms_user WHERE username LIKE 'r__g';-- [字符集合] [lto]SELECT * FROM cms_user WHERE username REGEXP '[lto]';-- [^字符集合] 除了字符集合中的内容SELECT * FROM cms_user WHERE username REGEXP '[^lto]';SELECT * FROM cms_user WHERE username REGEXP '[^l]';INSERT cms_user(username,password,regTime,proId)VALUES('lll','lll',138212349,2),('ttt','lll',138212349,2),('ooo','lll',138212349,2);SELECT * FROM cms_user WHERE username REGEXP '[a-k]';SELECT * FROM cms_user WHERE username REGEXP '[^a-m]';SELECT * FROM cms_user WHERE username REGEXP 'ng|qu';SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';SELECT * FROM cms_user WHERE username REGEXP 'que*';SELECT * FROM cms_user WHERE username REGEXP 't+';SELECT * FROM cms_user WHERE username REGEXP 'que+';SELECT * FROM cms_user WHERE username REGEXP 'que{2}';SELECT * FROM cms_user WHERE username REGEXP 'que{3}';SELECT * FROM cms_user WHERE username REGEXP 'que{1,3}';SELECT CONCAT('_',TRIM(' ABC '),'_'),CONCAT('_',LTRIM(' ABC '),'_'),CONCAT('_',RTRIM(' ABC '),'_');SELECT id,username,score, CASE WHEN score>60 THEN '不错' WHEN score=60 THEN '刚及格' ELSE '没及格' END FROM student;INSERT student(username,score) VALUES('AAAA',12);