MySQL
菜鸟教程：https://www.runoob.com/mysql/mysql-tutorial.html
注意事项
在Maven项目中，mysql server版本要和connector驱动版本一致！！！
mysql8.0以上需要设置serverTimezone：
jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
MySQL
varchar(n) n个字符长度无论中英文！
OS: win10
在解压目录下新建my.ini时需要用记事本打开并重新另存为ANSI编码！！！
[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
mysqld --initialize --console
这里需要记住root的初始密码
如果这里因为内存分配原因无法初始化，在配置文件中添加如下，数值根据机器情况
innodb_buffer_pool_size =50M
key_buffer_size =48M
mysqld install
net start mysql
最后登陆mysql修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
OS：ubuntu
安装教程：https://www.linode.com/docs/databases/mysql/install-mysql-on-ubuntu-14-04/
登录
mysql -u root -p 回车输入密码
退出
exit或quit
查看端口
show global variables like 'port';
修改端口
sudo vim /etc/mysql/my.cnf
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306 #在这里修改端口
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
然后重启
sudo /etc/init.d/mysql restart
查看字符集
show variables like 'char%';
修改字符集
sudo vim /etc/mysql/my.cnf
在[mysqld]下追加：
character-set-server=utf8
然后重启
sudo /etc/init.d/mysql restart
常用命令
关于UTF-8
ASCII字符（字母、数字、标点符合等）--1个字节
中文--3个字节
CREATE TABLE IF NOT EXISTS test_varchar(
a CHAR(5) , --数据最多5个字节
b VARCHAR(5) --数据最多5个字节，实际占用空间+1个字节，因为要存储长度。
)ENGINE=INNODB CHARSET=UTF8;
INSERT test_varchar (a,b) VALUES ('123','123');
INSERT test_varchar (a,b) VALUES ('12345','12345');
INSERT test_varchar (a,b) VALUES ('123456','123456'); //warnings
INSERT test_varchar (a,b) VALUES (' 123 ',' 123 '); --写入时，CHAR不会保留末尾空格，而VARCAHR会。
DEFAULT在语句中代表当前字段的默认值，可直接使用。
多表查询超过3个时不建议使用，效率较低，建议单表查询再合并。
外键的作用：保证数据的一致性和完整性。
1.不能插入超过父表主键范围的数据
2.不能删除父表
3.不能在父表中更新和子表有交集的数据（在没有指定级联CASCADE的情况下）
4.父表可以插入新数据
非法数据清洗完才能动态添加外键。
可视化工具： mysql workbench
# 注释内容
-- 注释内容
-- 创建数据库 king
CREATE DATABASE king;
-- 查看当前服务器下有哪些数据库
SHOW DATABASES;
SHOW SCHEMAS;
-- 查看king数据库的详细信息
SHOW CREATE DATABASE king;
-- 创建数据库queen 并且指定编码方式为gbk
CREATE DATABASE IF NOT EXISTS queen DEFAULT CHARACTER SET 'GBK';
-- 查看当前服务器下全部的数据库
SHOW DATABASES;
-- 查看queen指定数据库的详细信息
SHOW CREATE DATABASE queen;
-- 将queen的数据库的编码方式改为utf8
ALTER DATABASE queen DEFAULT CHARACTER SET 'UTF8';
-- 打开queen的数据库
USE queen;
-- 得到当前打开的数据库
SELECT DATABASE();
SELECT SCHEMA();
-- 删除king数据库
DROP DATABASE IF EXISTS king;
DROP DATABASE IF EXISTS queen;
-- 创建慕课网 imooc
CREATE DATABASE IF NOT EXISTS imooc DEFAULT CHARACTER SET 'UTF8';
-- 打开imooc
USE imooc;
-- 用户表 imooc_user
CREATE TABLE IF NOT EXISTS imooc_user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;
-- 查看imooc_user表的表结构
DESC imooc_user;
DESCRIBE imooc_user;
SHOW COLUMNS FROM imooc_user;
-- 删除imooc_user表
DROP TABLE IF EXISTS imooc_user;
-- 查看当前数据库下的数据表
-- 查看imooc_user表的详细信息
-- 通过三种方式查看指定数据表的表结构
-- 最后删除imooc_user数据表
-- 用户表 imooc_user
CREATE TABLE IF NOT EXISTS imooc_user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;
-- INSERT [INTO] tbl_name(id,username,...) VALUES(1,'KING',...);
-- 向imooc_user表插入一条记录
INSERT imooc_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(1,'king','king','382771946@qq.com',24,'230112197809871234','18635579617',88888.68,0,'北京','男');
-- 查询表中所有记录 SELECT * FROM tbl_name;
SELECT * FROM imooc_user;
INSERT imooc_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(-5,'king','123456','382771946@qq.com',190,'230112197809871234','18635579617',2345.68,1,'北京','女');
-- 测试整型
CREATE TABLE test_int(
a tinyint,
b smallint,
c mediumint,
d int,
e bigint
);
INSERT test_int(a) VALUES(-128);
-- 如果超出数据的范围，会产生截断现象
INSERT test_int(a) VALUES(-129);
INSERT test_int(a) VALUES(1270);
-- 测试无符号
CREATE TABLE test_unsigned(
a tinyint,
b tinyint UNSIGNED
);
INSERT test_unsigned(a,b) VALUES(-12,-12);
INSERT test_unsigned(a,b) VALUES(0,0);
INSERT test_unsigned(a,b) VALUES(0,256);
-- 测试零填充 ZEROFILL
CREATE TABLE test_int1(
a tinyint ZEROFILL,
b smallint ZEROFILL,
c mediumint ZEROFILL,
d int ZEROFILL,
e bigint ZEROFILL
);
INSERT test_int1(a,b,c,d,e) VALUES(1,1,1,1,1);
CREATE TABLE test_int2(
a tinyint(2),
b smallint(2)
);
INSERT test_int2(a,b) VALUES(123,45678);
-- 测试浮点型
CREATE TABLE test_float(
a FLOAT(6,2),
b DOUBLE(6,2),
c DECIMAL(6,2)
);
INSERT test_float(a,b,c) VALUES(4.143,4.146,4.149);
CREATE TABLE test_float1(
a FLOAT,
b DOUBLE,
c DECIMAL
);
INSERT test_float1(a,b,c) VALUES(4.143,4.146,4.649);
INSERT test_float(a,b,c) VALUES(1234567.89,2345678.9,567890.89);
-- 测试char 和 varchar
CREATE TABLE test_str(
a CHAR(5),
b VARCHAR(5)
);
INSERT test_str(a,b) VALUES('','');
INSERT test_str(a,b) VALUES('a','a');
INSERT test_str(a,b) VALUES('ab','ab');
INSERT test_str(a,b) VALUES('abc','abc');
INSERT test_str(a,b) VALUES('abcd','abcd');
INSERT test_str(a,b) VALUES('abcde','abcde');
INSERT test_str(a,b) VALUES('abcdef','abcdef');
INSERT test_str(a,b) VALUES(' 123 ',' 123 ');
SELECT CONCAT('*',a,'*'),CONCAT('*',b,'*') FROM test_str;
-- 测试TEXT不能有默认值
CREATE TABLE test_str1(
content TEXT DEFAULT 'THIS IS A TEST'
);
-- 测试ENUM
CREATE TABLE test_enum1(
sex ENUM('男 ','女 ','保密 ')
);
INSERT test_enum(sex) VALUES('男');
INSERT test_enum(sex) VALUES('男1');
INSERT test_enum(sex) VALUES(NULL);
INSERT test_enum(sex) VALUES(1);
INSERT test_enum(sex) VALUES(3);
INSERT test_enum(sex) VALUES(5);
-- 测试SET
CREATE TABLE test_set(
a SET('A','B','C','D','E','F')
);
INSERT test_set(a) VALUES('A');
INSERT test_set(a) VALUES('C');
INSERT test_set(a) VALUES('C,D,E');
INSERT test_set(a) VALUES('C,F,A');
INSERT test_set(a) VALUES('C,F,A,H');
INSERT test_set(a) VALUES(2);
-- HH:MM:SS [D HH:MM:SS] D表示天数 0~34
-- 测试TIME类型
CREATE TABLE test_time(
a TIME
);
INSERT test_time(a) VALUES('12:23:45');
INSERT test_time(a) VALUES('2 12:23:45');--+24H*2
INSERT test_time(a) VALUES('22:22'); --HH:MM
INSERT test_time(a) VALUES('22'); --SS
INSERT test_time(a) VALUES('2 22');--HH
-- HHMMSS
INSERT test_time(a) VALUES('121212');
INSERT test_time(a) VALUES('0');
INSERT test_time(a) VALUES(0);
INSERT test_time(a) VALUES('787878');
INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME);
-- 测试DATE类型 YYYY-MM-DD YYYYMMDD
CREATE TABLE test_date(
a DATE
);
INSERT test_date(a) VALUES('2017-03-04');
INSERT test_date(a) VALUES('2017-2-13');
INSERT test_date(a) VALUES('4007-03-23');
INSERT test_date(a) VALUES('40071212');
INSERT test_date(a) VALUES('4007@12@12');
INSERT test_date(a) VALUES('4008#4#13');
INSERT test_date(a) VALUES('4009.8.14');
-- YY-MM-DD YYMMDD
-- 70~99 转换成1970~1999 00~69 2000~2069
INSERT test_date(a) VALUES('780902');
INSERT test_date(a) VALUES('650902');
INSERT test_date(a) VALUES('880902');
INSERT test_date(a) VALUES(NOW());
INSERT test_date(a) VALUES(CURRENT_DATE);
-- 测试DATETIME
CREATE TABLE test_datetime(
a DATETIME
);
INSERT test_datetime(a) VALUES('1004-09-12 13:24:56');
INSERT test_datetime(a) VALUES('720305121212');
INSERT test_datetime(a) VALUES(NOW());
INSERT test_datetime(a) VALUES(DATETIME); --不存在
-- 测试TIMESTAMP
CREATE TABLE test_timestamp(
a TIMESTAMP
);
INSERT test_timestamp(a) VALUES('1978-10-23 12:12:12');
-- 1.插入CURRENT_TIMESTAMP
INSERT test_timestamp VALUES(CURRENT_TIMESTAMP);
-- 2.插入NULL
INSERT test_timestamp VALUES(NULL);
-- 3.什么也不写 得到当前系统日期和时间
INSERT test_timestamp VALUES();
--上述3条均为插入当前时间
-- 测试YEAR
CREATE TABLE test_year(
a YEAR
);
INSERT test_year(a) VALUES(1901);
-- 00~69 2000~2069 70~99 1970~1999
-- 0 插入的结果是0000
-- '0' 插入的结果是2000
-- 测试主键
CREATE TABLE test_primary_key(
id INT UNSIGNED PRIMARY KEY,
username VARCHAR(20)
);
INSERT test_primary_key(id,username) VALUES(1,'king');
INSERT test_primary_key(id,username) VALUES(1,'king123');
INSERT test_primary_key(username) VALUES('QUEEN');
CREATE TABLE test_primary_key1(
id INT UNSIGNED KEY, --PRIMARY可以省略
username VARCHAR(20)
);
CREATE TABLE test_primary_key2(
id INT UNSIGNED,
username VARCHAR(20),
PRIMARY KEY(id)
);
-- CREATE TABLE test_primary_key3(
-- id INT UNSIGNED PRIMARY KEY,
-- courseId INT UNSIGNED PRIMARY KEY,
-- username VARCHAR(20),
-- email VARCHAR(50)
-- );
-- 复合主键
CREATE TABLE test_primary_key3(
id INT UNSIGNED,
courseId VARCHAR(20),
username VARCHAR(20),
email VARCHAR(50),
PRIMARY KEY(id,courseId) --两个字段必须同时相同才算重复!
);
-- 1-a
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'a','king','382771946@qq.com');
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'b','king','382771946@qq.com');
INSERT test_primary_key3(id,courseId,username,email)
VALUES(2,'a','king','382771946@qq.com');
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'a','1king','1382771946@qq.com');
-- 测试AUTO_INCREMENT
CREATE TABLE test_auto_increment(
id INT UNSIGNED KEY AUTO_INCREMENT, --只能作用于主键！
username VARCHAR(20)
);
INSERT test_auto_increment(username) VALUES('A');
INSERT test_auto_increment(username) VALUES('B');
INSERT test_auto_increment(username) VALUES('C');
INSERT test_auto_increment(id,username) VALUES(NULL,'E');
INSERT test_auto_increment(id,username) VALUES(DEFAULT,'F');
INSERT test_auto_increment(id,username) VALUES(15,'G');
-- 测试非空
CREATE TABLE test_not_null(
a varchar(20),
b varchar(20) not null
);
INSERT test_not_null(a,b) VALUES('','');
INSERT test_not_null(a,b) VALUES(NULL,NULL);
INSERT test_not_null(a,b) VALUES(NULL,'abc');
INSERT test_not_null(a) VALUES('TEST');
-- 测试默认值
CREATE TABLE test_default(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com'
);
INSERT test_default(username) VALUES('A');
INSERT test_default(username,age,email) VALUES('B',30,'imooc@qq.com');
INSERT test_default(username,age,email) VALUES('C',NULL,'imooc@qq.com');
INSERT test_default(username,age,email) VALUES('D',NULL,NULL);
INSERT test_default(username,age,email) VALUES('D',NULL,DEFAULT);
CREATE TABLE test_default1(
id INT UNSIGNED AUTO_INCREMENT KEY,
sex ENUM('a','b','c') NOT NULL DEFAULT 'a'
);
-- 测试UNIQUE KEY
CREATE TABLE test_unique(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
email VARCHAR(50) UNIQUE,
card CHAR(18) UNIQUE
);
INSERT test_unique(username,email,card) VALUES('A','A@QQ.COM','1');
INSERT test_unique(username,email,card) VALUES('A','1A@QQ.COM','12');
INSERT test_unique(username,email,card) VALUES('B',NULL,NULL);
INSERT test_unique(username,email,card) VALUES('C',NULL,NULL);
-- 用户表imooc_user
CREATE TABLE IF NOT EXISTS `imooc_user`(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
`password` CHAR(32) NOT NULL COMMENT '密码',
`email` VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱',
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
`sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
`tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
`addr` VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
`card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
`married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已结婚',
`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
-- 测试布尔类型
CREATE TABLE test_bool(
id int,
married BOOL
);
CREATE TABLE test_bool1(
id int,
married BOOLEAN
);
-- 测试添加和删除字段
CREATE TABLE IF NOT EXISTS user1(
id INT UNSIGNED AUTO_INCREMENT KEY
);
-- 添加用户名字段 username VARCHAR(20)
ALTER TABLE user1
ADD username VARCHAR(20);
-- 添加密码字段 password CHAR(32) NOT NULL
ALTER TABLE user1
ADD password CHAR(32) NOT NULL;
-- 添加邮箱字段 email VARCHAR(50) NOT NULL UNIQUE 加到username之后
ALTER TABLE user1
ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;
-- 添加测试字段 test TINYINT(1) NOT NULL DEFAULT 0; 加到首位
ALTER TABLE user1
ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;
-- 删除test字段
ALTER TABLE user1
DROP test;
-- 添加age、addr字段，删除email字段
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP email;
-- 创建数据表 id / proName
-- 添加price FLOAT(8,2) UNSIGNED NOT NULL DEFAULT 0
-- 添加num INT UNSIGNED NOT NULL DEFAULT 100
-- 添加测试字段test VARCHAR(50) NOT NULL 加到首位
-- 添加测试字段test1 CHAR(23) NOT NULL 添加到price之后
-- 删除test字段
-- 选中一次数据表完成多次操作，添加一个desc TEXT, 添加isSale 布尔类型 0下架 1代表在卖
-- 删除test1字段
-- 测试添加删除默认值操作
CREATE TABLE user2(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 18,
email VARCHAR(50) NOT NULL
);
-- 给email字段添加默认值 imooc@qq.com
ALTER TABLE user2
ALTER email SET DEFAULT 'imooc@qq.com';
-- 删除age字段的默认值
ALTER TABLE user2
ALTER age DROP DEFAULT;
-- 添加地址字段
-- 给地址字段添加默认值为北京
-- 删除商品数量的默认值
-- 测试修改字段类型和字段属性、字段名称
CREATE TABLE user3(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(5) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(10) NOT NULL
);
-- 将用户名字段的类型改为20
ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL;
-- 将密码的长度改为40
ALTER TABLE user3
MODIFY password CHAR(40) NOT NULL;
-- 将email字段改为VARCHAR(50) NOT NULL FIRST
ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;
-- 将username 名称改为user
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL;
-- 将password 名称改为pwd
ALTER TABLE user3
CHANGE password pwd CHAR(40) NOT NULL;
-- 将email改成userEmail 类型改成VARCHAR(100) DEFAULT 'imooc@qq.com'
ALTER TABLE user3
CHANGE email userEmail VARCHAR(100) DEFAULT 'imooc@qq.com';
-- 测试添加和删除主键
CREATE TABLE user4(
id INT UNSIGNED,
username VARCHAR(20) NOT NULL
);
-- 添加主键
ALTER TABLE user4
ADD PRIMARY KEY(id);
-- 删除主键
ALTER TABLE user4
DROP PRIMARY KEY;
CREATE TABLE user5(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL
);
-- 将id的AUTO_INCREMENT去掉
ALTER TABLE user5
MODIFY id INT UNSIGNED;
-- 测试添加和删除唯一
CREATE TABLE user6(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE
);
-- 删除唯一索引 username 和email
ALTER TABLE user6
DROP INDEX username;
ALTER TABLE user6
DROP INDEX email;
-- 添加唯一索引
ALTER TABLE user6
ADD UNIQUE KEY(username);
ALTER TABLE user6
ADD UNIQUE INDEX uni_email(email);
-- 将user6改为user666
ALTER TABLE user6
RENAME TO user666;
ALTER TABLE user666
RENAME AS user6;
ALTER TABLE user6
RENAME user666;
RENAME TABLE user666 TO user6;
-- 创建数据表user id tinyint 字段
-- 添加一个username 字段 varchar(5) not null
-- 一次添加2个字段 password char(32) not null email varchar(10) not null
-- 给email字段添加默认值 'imooc@qq.com'
-- 将username字段改为username 类型varchar(20) not null
-- 将password字段改为password char(40) not null
-- 给id字段主键索引
-- 给id增加AUTO_INCREMENT属性
-- 给username字段添加唯一索引
-- 给email字段添加唯一索引名称是uni_email
-- 在一次添加2个字段 age TINYINT UNSIGNEED DEFAULT 18 加到password之后
-- addr VARCHAR(20) NOT NULL DEFAULT '北京'
-- 删除addr的默认值
-- 删除password唯一索引
-- 删除email唯一索引
-- 删除主键索引
-- 重命名user表叫new_user
-- 将new_user重命名回user
-- 测试MEMORY存储引擎
CREATE TABLE m1(
a VARCHAR(100)
)ENGINE=MEMORY;
-- 测试CSV存储引擎
CREATE TABLE c1(
a VARCHAR(20) NOT NULL,
b VARCHAR(20) NOT NULL
)ENGINE=CSV;
INSERT c1(a,b) VALUES('AAA','BBB');
INSERT c1(a,b) VALUES('CCC','DDD');
-- 测试ARCHIVE存储引擎
CREATE TABLE a1 AS SELECT * FROM INFORMATION_SCHEMA.columns;
INSERT INTO a1 SELECT * FROM a1;
-- 11026432 57177
CREATE TABLE a2 ENGINE=ARCHIVE AS SELECT * FROM a1;
-- 742354 55392
-- 测试MyISAM存储引擎
CREATE TABLE test_myisam(
a INT UNSIGNED,
b VARCHAR(20),
c CHAR(32)
)ENGINE=MyISAM;
CREATE TABLE myisam_1(
a char(30),
id int
)ENGINE=MyISAM;
CREATE TABLE myisam_2(
a VARCHAR(30),
id int
)ENGINE=MyISAM;
CREATE TABLE myisam_3(
a VARCHAR(30),
id int
)ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE test_innodb(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL
);
-- 测试添加记录
CREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET 'UTF8';
USE king;
CREATE TABLE IF NOT EXISTS user(
id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
email VARCHAR(50) NOT NULL DEFAULT 'imooc@qq.com' COMMENT '邮箱'
)ENGINE=INNODB CHARSET=UTF8;
-- 不指定字段名称
INSERT user VALUE(1,'king',24,'382771946@qq.com');
INSERT user VALUES(NULL,'queen',25,'queen@qq.com');
INSERT user VALUES(DEFAULT,'lily',26,'lily@qq.com');
-- 列出指定字段的形式
INSERT user(username,email) VALUES('rose','rose@qq.com');
INSERT user(age,email,id,username) VALUES(34,'imooc@qq.com',5,'imooc');
-- 一次插入3条记录
INSERT user VALUES(NULL,'a',DEFAULT,DEFAULT),
(NULL,'b',56,'b@qq.com'),
(NULL,'c',14,'c@qq.com');
-- INSERT ...SET 的形式
INSERT user SET username='d',age=45,email='d@qq.com';
-- INSERT SELECT
INSERT user(username) SELECT a FROM test;
-- 测试更新语句
-- 修改第一个用户的信息 id=1
UPDATE user SET age=29 WHERE id=1;
-- 修改id=3的用户，username age email
UPDATE user SET age=47,email='lilys@qq.com',username='lilys' WHERE id=3;
-- 所有用户年龄+10
UPDATE user SET age=age+10;
-- 将id<=5的用户年龄改为-20，将邮箱改为默认值
UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;
-- 测试删除语句
-- 删除用户名为king
DELETE FROM user WHERE username='king';
-- 删除年龄为24的用户
DELETE FROM user WHERE age=24;
-- 删除表中所有记录
DELETE FROM user;
INSERT user VALUES(NULL,'queen',25,'queen@qq.com');
INSERT user VALUES(DEFAULT,'lily',26,'lily@qq.com');
-- 测试查询操作
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已婚',
salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
INSERT user1 VALUES(1,'king',23,'男','北京',1,50000);
INSERT user1(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);
INSERT user1 SET username='imooc',age=31,sex='女',addr='北京',salary=40000;
INSERT user1 VALUES(NULL,'张三',38,'男','上海',0,15000),
(NULL,'张三风',38,'男','上海',0,15000),
(NULL,'张子怡',39,'女','北京',1,85000),
(NULL,'汪峰',42,'男','深圳',1,95000),
(NULL,'刘德华',58,'男','广州',0,115000),
(NULL,'吴亦凡',28,'男','北京',0,75000),
(NULL,'奶茶妹',18,'女','北京',1,65000),
(NULL,'刘嘉玲',36,'女','广州',0,15000);
-- 查询表中所有记录
SELECT * FROM user1;
-- username,addr,age
SELECT username,addr,age FROM user1;
-- 查询king数据库下user1表中的所有记录
SELECT * FROM king.user1;
-- 查询user1表中的id 编号 username 用户名 sex 性别
SELECT id AS '编号',username AS '用户名', sex AS '性别'
FROM user1;
SELECT id age,age id,username FROM user1;
-- 给表起别名
SELECT id,username FROM user1 AS u;
-- 测试表名.字段名
SELECT user1.id,user1.username,user1.age FROM user1 ;
SELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;
-- 测试WHERE 条件的比较运算符
-- 查询id,username,age id=5的用户
SELECT id,username,age FROM user1
WHERE id=5;
SELECT id,username,age FROM user1
WHERE id=50;
-- 添加desc字段 VARCHAR(100)
ALTER TABLE user1
ADD userDesc VARCHAR(100);
-- 更新id<=9的用户 userDesc='this is a test'
UPDATE user1 SET userDesc='this is a test'
WHERE id<=9;
-- 查询用户userDesc 为NULL的用户
SELECT id,username,age,userDesc FROM user1
WHERE userDesc=NULL;
-- 检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc<=>NULL;
-- IS [NOT] NULL检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NULL;
-- 测试范围BETWEEN AND
-- 查询年龄在18~30之间的用户
SELECT id,username,age,sex FROM user1
WHERE age BETWEEN 18 AND 30;
-- 查询薪水在10000~50000之间的用户
SELECT id,username,age,salary FROM user1
WHERE salary BETWEEN 10000 AND 50000;
SELECT id,username,age,salary FROM user1
WHERE salary NOT BETWEEN 10000 AND 50000;
-- 测试指定集合 IN
-- 查询编号为1,3,5,7,9
SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9,29,45,78);
SELECT id,username,age FROM user1
WHERE username IN('king','queen','lily','rose');
-- 测试逻辑运算符
-- 查询性别为男并且年龄>=20的用户
SELECT id,username,age,sex FROM user1
WHERE sex='男' AND age>=20;
-- id>=5 && age<=30
SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30;
SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30 AND sex='男';
-- 要求sex='女' 并且 addr='北京'
SELECT id,username,age,sex,addr FROM user1
WHERE sex='女' AND addr='北京';
-- 查询薪水范围在60000~10000并且性别为男 addr='北京'
SELECT id,username,age,sex,salary,addr FROM user1
WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';
-- 查询id=1 或者 用户名为queen
SELECT id,username,age FROM user1
WHERE id=1 OR username='queen';
-- 测试模糊查询
SELECT id,username,age FROM user1
WHERE username='king';
SELECT id,username,age FROM user1
WHERE username LIKE 'king';
-- 要求用户名中包含三
SELECT id,username,age,sex FROM user1
WHERE username LIKE '%三%';
-- 用户名中包含n
SELECT id,username,age FROM user1
WHERE username LIKE '%in%';
-- 要求查询出姓张的用户
SELECT id,username,age FROM user1
WHERE username LIKE '张%';
-- 查询以风结尾的用户
SELECT id,username,age FROM user1
WHERE username LIKE '%风';
-- 用户名长度为三位的用户
SELECT id,username,age,sex FROM user1
WHERE username LIKE '___';
SELECT id,username,age,sex FROM user1
WHERE username LIKE '张_';
SELECT id,username,age,sex FROM user1
WHERE username LIKE '张_%';
-- 测试分组
-- 按照性别分组sex
SELECT id,username,age,sex FROM user1
GROUP BY sex;
-- 按照addr分组
SELECT username,age,sex,addr FROM user1
GROUP BY addr;
-- 按照性别分组，查询组中的用户名有哪些
SELECT GROUP_CONCAT(username),age,sex,addr FROM user1
GROUP BY sex;
SELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1
GROUP BY sex;
-- 测试COUNT()
SELECT COUNT(*) FROM user1;
SELECT COUNT(id) FROM user1;
-- 按照sex分组，得到用户名详情，并且分别组中的总人数
SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1
GROUP BY sex;
-- 按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值和
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
GROUP BY addr;
-- 按照sex分组，统计组中总人数、用户名详情，得到薪水总和，薪水最大值、最小值、平均值
SELECT sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY sex;
SELECT GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
WITH ROLLUP;
-- 按照字段的位置来分组
SELECT id,sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY 2;
-- 查询age>=30的用户并且按照sex分组
SELECT sex,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
WHERE age>=30
GROUP BY sex;
-- 按照addr分组，统计总人数
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr;
-- 对于分组结果进行二次筛选，条件是组中总人数>=3
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING COUNT(*)>=3;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING totalUsers>=3;
-- 按照addr分组，
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr;
-- 要求平均薪水>=40000
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr
HAVING avg_salary>=40000;
-- 测试排序
-- 按照id降序排列
SELECT id,username,age
FROM user1
ORDER BY id DESC;
-- 按照age升序
SELECT id,username,age
FROM user1
ORDER BY age ;
-- 按照多个字段排序
SELECT id,username,age
FROM user1
ORDER BY age ASC,id ASC;
-- 测试条件+排序
SELECT id,username,age
FROM user1
WHERE age>=30;
SELECT id,username,age
FROM user1
WHERE age>=30
ORDER BY age DESC;
-- 实现随机记录
SELECT id,username,age
FROM user1
ORDER BY RAND();
-- 测试LIMIT语句
-- 显示结果集的前5条记录
SELECT id,username,age,sex
FROM user1
LIMIT 5;
SELECT id,username,age,sex
FROM user1
LIMIT 0,5;
-- 显示前3条记录
SELECT id,username,age,sex
FROM user1
LIMIT 0,3;
SELECT id,username,age,sex
FROM user1
LIMIT 3,3;
-- 更新前3条记录，将age+5
UPDATE user1 SET age=age+5 LIMIT 3;
-- 按照id降序排列，更新前三条记录，将age-10
UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;
-- 删除前三条记录
DELETE FROM user1
LIMIT 3;
DELETE FROM user1
ORDER BY id DESC
LIMIT 3;
-- 测试完整SELECT 语句的形式
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC
LIMIT 0,2;
CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8';
USE test2;
CREATE TABLE emp(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
)ENGINE=INNODB CHARSET=UTF8;
INSERT emp(username,age,depId) VALUES('king',24,1),
('queen',25,2),
('imooc',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);
INSERT emp(username,age,depId) VALUES('测试用户',39,6);
CREATE TABLE dep(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(50) NOT NULL UNIQUE,
depDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB CHARSET=UTF8;
INSERT dep(depName,depDesc) VALUES('PHP教学部','研发PHP课件'),
('JAVA教学部','研发JAVA课件'),
('WEB前端教学部','研发WEB前端课件'),
('IOS教学部','研发IOS课件');
-- 查询 emp id username age 部门名称 dep depName
SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;
SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;
-- 查询emp id username age addr dep id depName depDesc
SELECT e.id,e.username,e.age,e.addr,
d.id,d.depName,d.depDesc
FROM dep AS d
JOIN emp AS e
ON d.id=e.depId;
-- 测试左外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;
-- 测试右外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;
-- 创建管理员表
CREATE TABLE user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
proName VARCHAR(10) NOT NULL DEFAULT '北京'
);
INSERT user(username,proName) VALUES('a','北京'),
('b','哈尔滨'),
('c','上海'),
('d','深圳'),
('e','广州'),
('f','重启');
-- 创建省份表
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳');
--
CREATE TABLE user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
proId TINYINT UNSIGNED NOT NULL
);
INSERT user(username,proId) VALUES('a',1);
INSERT user(username,proId) VALUES('b',1);
INSERT user(username,proId) VALUES('c',1);
INSERT user(username,proId) VALUES('d',2);
INSERT user(username,proId) VALUES('e',3);
INSERT user(username,proId) VALUES('f',1);
INSERT user(username,proId) VALUES('g',1);
-- 查询user id ,username provinces proName
SELECT u.id,u.username,p.proName
FROM user AS u
JOIN provinces AS p
ON u.proId=p.id;
-- 创建省份表
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
-- 管理员admin id username email proId
CREATE TABLE admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
proId TINYINT UNSIGNED NOT NULL
);
INSERT admin(username,proId) VALUES('king',1);
INSERT admin(username,proId) VALUES('queen',2);
-- 商品分类cate id cateName cateDesc
CREATE TABLE cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);
INSERT cate(cateName) VALUES('母婴');
INSERT cate(cateName) VALUES('服装');
INSERT cate(cateName) VALUES('电子');
-- 商品表products id productName, price,cateId
CREATE TABLE products(
id INT UNSIGNED AUTO_INCREMENT KEY,
productName VARCHAR(50) NOT NULL UNIQUE,
price FLOAT(8,2) NOT NULL DEFAULT 0,
cateId TINYINT UNSIGNED NOT NULL,
adminId TINYINT UNSIGNED NOT NULL
);
INSERT products(productName,price,cateId,adminId)
VALUES('iphone9',9888,3,1),
('adidas',388,2,2),
('nike',888,2,2),
('奶瓶',288,1,1);
-- 查询products id productName price --- cate cateName
SELECT p.id,p.productName,p.price,c.cateName
FROM products AS p
JOIN cate AS c
ON p.cateId=c.id;
-- 查询管理员 id username email -- provinces proName
SELECT a.id,a.username,a.email,p.proName
FROM admin AS a
JOIN provinces AS p
ON a.proId=p.id;
-- 查询 products id productName price
-- cate cateName
-- admin username email
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
JOIN admin AS a
ON p.adminId=a.id
JOIN cate AS c
ON p.cateId=c.id
WHERE p.price<1000
ORDER BY p.price DESC
LIMIT 0,2;
-- products id productName price
-- cate cateName
-- admin username email
-- provinces proName
-- 测试外键
-- 新闻分类表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);
-- 新闻表 news
CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL
);
INSERT news_cate(cateName) VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');
INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);
-- 查询news id title content
-- news_cate cateName
SELECT n.id,n.title,n.content,c.cateName
FROM news AS n
JOIN news_cate AS c
ON n.cateId=c.id;
INSERT news(title,content,cateId) VALUES('a6','aaaa6',45);
-- 添加外键
-- 新闻分类表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB;
-- 新闻表 news
CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL,
FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;
INSERT news_cate(cateName) VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');
INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);
-- 测试非法记录
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);
-- 测试删除父表中的记录 和删除父表
DELETE FROM news_cate WHERE id=1;
UPDATE news_cate SET id=10 WHERE id=1;
INSERT news_cate(cateName) VALUES('教育新闻');
-- 将教育新闻 改成教育
UPDATE news_cate SET cateName='教育' WHERE id=5;
UPDATE news_cate SET id=50 WHERE cateName='教育';
-- 添加外键名称
-- 新闻分类表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB;
-- 新闻表 news
CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL,
CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;
-- 删除外键
ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;
-- 添加外键
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);
-- 删除外键
ALTER TABLE news
DROP FOREIGN KEY news_ibfk_1;
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);
INSERT news_cate(cateName) VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');
INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',8);
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);
-- 指定级联操作 DELETE CASCADE UPDATE CASCADE
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;
-- 测试子查询
-- 测试由IN引发的子查询
SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);
SELECT * FROM emp
WHERE depId NOT IN (SELECT id FROM dep);
-- 学员stu
CREATE TABLE stu(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED NOT NULL
);
INSERT stu(username,score) VALUES('king',95),
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',98),
('ceshi',99),
('tiancai',50);
-- 分数级别level
CREATE TABLE level(
id tinyint UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED COMMENT '分数'
);
INSERT level(score) VALUES(90),(80),(70);
-- 查询出成绩优秀的学员
SELECT score FROM level WHERE id=1;
SELECT id,username,score FROM stu
WHERE score>=(SELECT score FROM level WHERE id=1);
-- 查询出没有得到评级的学员
SELECT id,username,score FROM stu
WHERE score<=(SELECT score FROM level WHERE id=3);
-- 由EXISTS 引发的子查询
SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);
SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=1);
-- 带有ANY SOME ALL关键字的子查询
SELECT * FROM stu
WHERE score>= ANY(SELECT score FROM level);
SELECT * FROM stu
WHERE score>= SOME(SELECT score FROM level);
SELECT * FROM stu
WHERE score>= ALL(SELECT score FROM level);
SELECT * FROM stu
WHERE score< ALL(SELECT score FROM level);
SELECT * FROM stu
WHERE score=ANY(SELECT score FROM level);
SELECT * FROM stu
WHERE score!=ALL(SELECT score FROM level);
-- 创建一个user1表,id username
CREATE TABLE user1(
id int UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20)
)SELECT id,username FROM emp;
-- 将user表中的用户名写入到user1表中
INSERT user1(username) SELECT username FROM user;
-- 将stu表中的tiancai用户名添加到user2表中
INSERT user2 SET username=(SELECT username FROM stu WHERE id=9);
-- 去掉字段的重复值
SELECT DISTINCT(username) FROM user2;
-- 将user1和user2数据合并到一起
SELECT * FROM user1
UNION
SELECT * FROM user2;
-- 测试自身连接
CREATE TABLE cate(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(100) NOT NULL UNIQUE,
pId SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT cate(cateName,pId) VALUES('服装',0);
INSERT cate(cateName,pId) VALUES('数码',0);
INSERT cate(cateName,pId) VALUES('箱包',0);
INSERT cate(cateName,pId) VALUES('男装',1);
INSERT cate(cateName,pId) VALUES('女装',1);
INSERT cate(cateName,pId) VALUES('内衣',1);
INSERT cate(cateName,pId) VALUES('电视',2);
INSERT cate(cateName,pId) VALUES('冰箱',2);
INSERT cate(cateName,pId) VALUES('洗衣机',2);
INSERT cate(cateName,pId) VALUES('爱马仕',3);
INSERT cate(cateName,pId) VALUES('LV',3);
INSERT cate(cateName,pId) VALUES('GUCCI',3);
INSERT cate(cateName,pId) VALUES('夹克',4);
INSERT cate(cateName,pId) VALUES('衬衫',4);
INSERT cate(cateName,pId) VALUES('裤子',4);
INSERT cate(cateName,pId) VALUES('液晶电视',10);
INSERT cate(cateName,pId) VALUES('等离子电视',10);
INSERT cate(cateName,pId) VALUES('背投电视',10);
-- 查询所有的分类信息，并且得到其父分类
SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId=p.id;
-- 查询所有的分类及其子分类
SELECT p.id,p.cateName AS pCateName,s.cateName AS sCateName
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId=p.id;
-- 查询所有的分类并且得到子分类的数目
SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS count
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId=p.id
GROUP BY p.cateName
ORDER BY id ASC;
sCate 表
id | cateName | pId |
+----+-----------------+-----+
| 1 | 服装 | 0 |
| 2 | 数码 | 0 |
| 3 | 玩具 | 0 |
| 4 | 男装 | 1 |
| 5 | 女装 | 1 |
| 6 | 内衣 | 1 |
| 10 | 电视 | 2 |
| 11 | 冰箱 | 2 |
| 12 | 洗衣机 | 2 |
| 13 | 爱马仕 | 3 |
| 14 | LV | 3 |
| 15 | GUCCI | 3 |
| 16 | 夹克 | 4 |
| 17 | 衬衫 | 4 |
| 18 | 裤子 | 4 |
| 19 | 液晶电视 | 10 |
| 20 | 等离子电视 | 10 |
| 21 | 背投电视 | 10
pCate
id | cateName | pId |
+----+-----------------+-----+
| 1 | 服装 | 0 |
| 2 | 数码 | 0 |
| 3 | 玩具 | 0 |
| 4 | 男装 | 1 |
| 5 | 女装 | 1 |
| 6 | 内衣 | 1 |
| 10 | 电视 | 2 |
| 11 | 冰箱 | 2 |
| 12 | 洗衣机 | 2 |
| 13 | 爱马仕 | 3 |
| 14 | LV | 3 |
| 15 | GUCCI | 3 |
| 16 | 夹克 | 4 |
| 17 | 衬衫 | 4 |
| 18 | 裤子 | 4 |
| 19 | 液晶电视 | 10 |
| 20 | 等离子电视 | 10 |
| 21 | 背投电视 | 10
-- 测试字符串函数
-- CHAR_LENGTH():得到字符串的字符数
SELECT CHAR_LENGTH('abc');
-- LENGTH():返回字符串的长度
SELECT LENGTH('abc');
-- CONCAT(s1,s2,....):将字符串合并成一个字符串
SELECT CONCAT('a','b','c');
-- 如果字符串中包含NULL，返回拼接结果就是NULL
SELECT CONCAT('a','b','c',null);
-- CONCAT_WS(x,s1,s2,s2....)：以指定分隔符拼接字符串
SELECT CONCAT_WS('-','a','b','c');
-- 如果null在拼接的内容中，则转化成空字符串
SELECT CONCAT_WS('-','a','b','c',null);
-- 如果分隔符为null，拼接的结果为null
SELECT CONCAT_WS(null,'a','b','c');
-- 将字符串转换成大写或者小写 UPPER()| UCASE() LOWER()|LCASE()
SELECT UPPER('hello king'),UCASE('hello imooc'),LOWER('HELLO ADMIN'),LCASE('HELLO EVERYBODY');
-- 字符串的反转REVERSE()
SELECT REVERSE('abc');
-- LEFT()|RIGHT():返回字符串的前几个字符或者后几个字符
SELECT LEFT('hello',2),RIGHT('hello',2);
-- LPAD()|RPAD():用字符串填充到指定长度
SELECT LPAD('abc',10,'?');
SELECT RPAD('abc',10,'!');
-- 去掉字符串两端的空格TRIM()|LTRIM()|RTRIM():
SELECT CONCAT('*',TRIM(' abc '),'*'),CONCAT('*',LTRIM(' abc '),'*'),CONCAT('*',RTRIM(' abc '),'*');
-- REPEAT():重复指定的次数
SELECT REPEAT('hello',3);
-- REPLACE():字符串
SELECT REPLACE('hello king','king','queen');
-- 截取字符串SUBSTRING
SELECT SUBSTRING('abcdef',1,3);
-- 比较字符串
SELECT STRCMP('a','b');
-- 测试日期时间函数
-- 返回当前日期
SELECT CURDATE(),CURRENT_DATE();
-- 返回当前时间
SELECT CURTIME(),CURRENT_TIME();
-- 返回当前的日期时间
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();
-- 返回日期中的月份和月份的名称
SELECT MONTH('2017-02-19');
SELECT MONTH(CURDATE()),MONTHNAME(CURDATE());
-- 返回星期几
SELECT DAYNAME(NOW());
-- 返回一周内的第几天,0代表星期一
SELECT DAYOFWEEK(NOW());
SELECT WEEK(NOW());
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
-- DATEDIFF()计算两个日期相差的天数
SELECT DATEDIFF('2017-03-06','2017-03-02');
-- 测试其它常用函数
SELECT VERSION(),CONNECTION_ID();
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
-- 得到上一步插入操作产生AUTO_INCREMENT的值
SELECT LAST_INSERT_ID();
SELECT MD5('king');
-- PASSWORD():密码加密算法
SELECT PASSWORD('root');
网友评论