MySQL应用

CREATE DATABASE IF NOT EXISTS cms DEFAULT CHARACTER SET utf8;
USE cms;
-- 管理员表cms_admin
CREATE 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_cate
CREATE 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_news
CREATE 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);

-- 创建身份表 provinces
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳'),
('广州'),
('重庆');

-- 创建用户表cms_user
CREATE 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_name
SELECT 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值为NULL
SELECT * FROM cms_user WHERE age=NULL;

SELECT * FROM cms_user WHERE age<=>NULL;

SELECT * FROM cms_user WHERE age<=>18;

-- IS NULL 或者IS NOT NULL
SELECT * 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，100
SELECT * 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为的3
SELECT * 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);

-- 按照用户所属身份分组proId
SELECT * 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，性别详情，注册时间详情，用户名详情 安装proId
SELECT 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_age
FROM cms_user
GROUP BY sex;

-- WITH ROLLUP
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex WITH ROLLUP;

SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex WITH ROLLUP;

--查询性别sex,用户名详情,组中总人数，最大年龄，年龄总和,根据性别分组
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM 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_age
FROM cms_user
GROUP BY sex
HAVING COUNT(*)>2;

-- 查询组中人数大于2并且最大年龄大于60的

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex
HAVING 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_age
FROM cms_user
WHERE id>=2
GROUP BY sex
HAVING 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_age
FROM cms_user
WHERE id>=2
HAVING COUNT(*)>2 AND MAX(age)>60;

-- 按照id降序排列DESC 默认的是ASC
SELECT * 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 sex
HAVING COUNT(*)>=2
ORDER 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_age
FROM cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 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
-- 课程描述courseDesc
CREATE 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;

-- 无符号UNSIGNED

CREATE TABLE test2(
num1 TINYINT UNSIGNED,
num2 TINYINT
);

INSERT test2 VALUES(0,-12);

-- 零填充ZEROFILL

CREATE 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和VARCHAR
CREATE 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_INCREMENT
CREATE 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 NULL
CREATE 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);

-- 测试DEFAULT

CREATE 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 KEY

CREATE 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重命名成user11
ALTER 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,test5
ALTER 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字段改为test1
ALTER 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;

-- 修改表的存储引擎为MyISAM
ALTER 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;


-- 测试插入记录INSERT
CREATE 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;

-- 将用户表中所有的用户年龄更新15
UPDATE user SET age=5;

UPDATE user SET age=20,email='test@qq.com';

-- 将第一个记录的password，email，age
UPDATE user SET password='king123',email='123@qq.com',age=99
WHERE 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位的用户，让其已有年龄-3

UPDATE cms_user SET age=age-3 WHERE username LIKE '____';

-- 更新前3条记录，让已有年龄+10
UPDATE 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,proName
SELECT cms_user.id,username,proName FROM cms_user,provinces;

-- cms_user的proId对应省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces

WHERE cms_user.proId=provinces.id;

-- 查询cms_user表中id,username,email,sex
-- 查询provinces表proName
SELECT u.id,u.username,u.email,u.sex,p.proName

FROM cms_user AS u

INNER JOIN provinces AS p

ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

CROSS JOIN cms_user AS u

ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

JOIN cms_user AS u

ON 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 u

JOIN

provinces AS p

ON u.proId=p.id

WHERE u.sex='男';

-- 根据proName分组
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)

FROM cms_user AS u

JOIN

provinces AS p

ON u.proId=p.id

WHERE 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 u

JOIN

provinces AS p

ON u.proId=p.id

WHERE u.sex='男'

GROUP BY p.proName

HAVING COUNT(*)>=1;

--　　按照id升序排列

SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)

FROM cms_user AS u

JOIN

provinces AS p

ON u.proId=p.id

WHERE u.sex='男'

GROUP BY p.proName

HAVING COUNT(*)>=1

ORDER BY u.id ASC;

-- 限制显示条数 前2条
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)

FROM cms_user AS u

JOIN

provinces AS p

ON u.proId=p.id

WHERE u.sex='男'

GROUP BY p.proName

HAVING COUNT(*)>=1

ORDER BY u.id ASC

LIMIT 0,2;

--　查询cms_news中的id,title,
--  查询cms_cate 中的cateName
SELECT n.id,n.title,c.cateName FROM 

cms_news AS n

JOIN 

cms_cate AS c

ON n.cId=c.id;

-- cms_news id,title
-- cms_admin username,role

SELECT n.id,n.title,a.username,a.role

FROM 

cms_news AS n

JOIN

cms_admin AS a

ON n.aId=a.id;

-- cms_news id ,title
-- cms_cate cateName
-- cms_admin username,role

SELECT n.id,n.title,c.cateName,a.username,a.role

FROM cms_cate AS c

JOIN 

cms_news AS n

ON n.cId=c.id

JOIN 

cms_admin AS a

ON 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.proName

FROM cms_user AS u

LEFT JOIN provinces AS p

ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

LEFT JOIN cms_user AS u

ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

RIGHT JOIN cms_user AS u

ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

RIGHT JOIN cms_user AS u

ON 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 ,depId
CREATE 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 FROM

employee AS e

JOIN

department AS d

ON 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 ,depId
CREATE 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 ,depId
CREATE 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 ,depId
CREATE 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 score
CREATE 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 ,level

CREATE 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);

SELECT id,username,score FROM student WHERE score<ANY(SELECT level FROM scholarship);

SELECT id,username,score FROM student WHERE score<=ANY(SELECT level FROM scholarship);

-- 相当于IN
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);

SELECT id,username,score FROM student WHERE score IN(SELECT level FROM scholarship);

-- 相当于NOT IN
SELECT id,username,score FROM student WHERE score NOT IN(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);
时间： 2024-09-15 10:04:46

MySQL应用的相关文章

php列出mysql表所有行和列的方法

 这篇文章主要介绍了php列出mysql表所有行和列的方法,涉及php操作mysql数据库的技巧,具有一定参考借鉴价值,需要的朋友可以参考下     本文实例讲述了php列出mysql表所有行和列的方法.分享给大家供大家参考.具体实现方法如下: 代码如下: <html> <head> <title>Selecting Data</title> </head> <body> <?php $user = "root&quo

mysql 复制表数据，表结构的3种方法

 什么时候我们会用到复制表?例如:我现在对一张表进行操作,但是怕误删数据,所以在同一个数据库中建一个表结构一样,表数据也一样的表,以作备份.如果用mysqldump比较麻烦,备份.MYD,.MYI这样的文件呢,操作起来也还是麻烦. 一,复制表结构 方法1: mysql> create table a like users; //复制表结构 Query OK, 0 rows affected (0.50 sec)   mysql> show tables; +------+ | Tables_i

Sequelize 和 MySQL 对照

如果你觉得Sequelize的文档有点多.杂,不方便看,可以看看这篇. 在使用NodeJS来关系型操作数据库时,为了方便,通常都会选择一个合适的ORM(Object Relationship Model)框架.毕竟直接操作SQL比较繁琐,通过ORM框架,我们可以使用面向对象的方式来操作表.NodeJS社区有很多的ORM框架,我比较喜欢Sequelize,它功能丰富,可以非常方便的进行连表查询. 这篇文章我们就来看看,Sequelize是如何在SQL之上进行抽象.封装,从而提高开发效率的. 安装

MySQL和MongoDB设计实例对比

MySQL是关系型数据库中的明星,MongoDB是文档型数据库中的翘楚.下面通过一个设计实例对比一下二者:假设我们正在维护一个手机产品库,里面除了包含手机的名称,品牌等基本信息,还包含了待机时间,外观设计等参数信息,应该如何存取数据呢? 如果使用MySQL的话,应该如何存取数据呢? 如果使用MySQL话,手机的基本信息单独是一个表,另外由于不同手机的参数信息差异很大,所以还需要一个参数表来单独保存. CREATE TABLE IF NOT EXISTS `mobiles` (     `id` 

mysql-求一个Mysql语句 查询出当前周的数据按照天分组

问题描述 求一个Mysql语句 查询出当前周的数据按照天分组 SELECT DATE_FORMAT(uploadTime_beg%Y-%m-%d"") as time SUM(field01) as sumStatus1 SUM(field02) as sumStatus2 SUM(field03) as sumStatus3 SUM(field04) as sumStatus4 SUM(field05) as sumStatus5 FROM health_realdata WHERE

在MYSQL中插入当前时间，就象SQLSERVER的GETDATE（）一样。（见内）

mysql|server|sqlserver|插入 NOW()函数以`'YYYY-MM-DD HH:MM:SS'返回当前的日期时间,可以直接存到DATETIME字段中.CURDATE()以'YYYY-MM-DD'的格式返回今天的日期,可以直接存到DATE字段中.CURTIME()以'HH:MM:SS'的格式返回当前的时间,可以直接存到TIME字段中.例:insert into tablename (fieldname) values (now()) 

Mysql字段长度，供出血者参考，呵呵！

mysql|参考 列类型  需要的存储量  TINYINT  1 字节 SMALLINT  2 个字节 MEDIUMINT  3 个字节 INT  4 个字节 INTEGER  4 个字节 BIGINT  8 个字节 FLOAT(X)  4 如果 X < = 24 或 8 如果 25 < = X < = 53 FLOAT  4 个字节 DOUBLE  8 个字节 DOUBLE PRECISION  8 个字节 REAL  8 个字节 DECIMAL(M,D)  M字节(D+2 , 如果M

在oracle中限制返回结果集的大小，类似于mysql的limit（转译）

mysql|oracle Oracle不支持类似于 MySQL 中的 limit. 但你还是可以rownum来限制返回的结果集的行数. 如果你只希望返回前十行纪录,你可以这样写: SELECT * FROM table WHERE ROWNUM<10; 但是下面的语句是不对的: SELECT * FROM table WHERE ROWNUM>90 AND ROWNUM<100; 这是因为 Oracle 认为这个条件不成立,所以没有返回.你应该这样写: SELECT * FROM tab

使用MySQL时的一些常见错误

mysql|错误 MySQL server has gone away错误本小节也涉及有关Lost connection to server during query的错误. 对MySQL server has gone away错误最常见的原因是服务器超时了并且关闭了连接.缺省地,如果没有事情发生,服务器在 8个小时后关闭连接.你可在启动mysqld时通过设置wait_timeout变量改变时间限制. 你可以通过执行mysqladmin version并且检验正常运行的时间来检查MySQL还没

视频出炉：4月15日《阿里云RDS MySQL分支深度定制实战分享》

活动视频 <阿里云RDS MySQL分支深度定制实战分享> PDF地址:https://oss.aliyuncs.com/yqfiles/a5344b5961b367786a95620c636c4640.pdf 分享简介:阿里云RDS MySQL经过多年的积累,不断的进行性能优化,并定制了适合不同行业需求的功能,同时也向官方和社区贡献力量.本次主题主要介绍RDS MySQL分支的深度定制,包括功能扩展.资源管控.性能优化.数据安全.行业解决方案等. 分享者:赵建伟,现任阿里云数据库内核资深研发