SELECT VERSION(); //显示服务器版本
SELECT NOW(); //显示当前时间
SELECT USER(); //显示当前用户
书写规范:关键字与函数名称全部大写,数据库表名称、库名称、字段名称全部小写。
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name; //创建数据库 []为可写可不写
SHOW {DATABASES|SCHEMAS} [LIKE 'pattern'|WHERE expr]; //查看服务器下数据表列表
SHOW CREATE DATABASE databasename; //显示数据库编码方式
ALTER {DATABASE|SCHEMA} [data_name] [DEFAULT] CHARACTER SET [=] charset_name; //修改编码方式
ALTER TABLE admin CONVERT TO CHARACTER SET utf8; //设置整个表中所有字段的编码方式为utf8
DROP {DATABASE|SCHEMA} [IF EXISTS] db_name; //删除数据库
mysql> CREATE TABLE tb1(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,//UNSIGNED 表示非负
-> salary FLOAT(8,2) UNSIGNED //FLOAT(8,2)一共8位,小数点后有2位 eg:9988.34
-> );
Query OK, 0 rows affected (0.07 sec)
SHOW TABLES[FROM db_name][LIKE 'pattern'|WHERE expr]; //查看数据表列表
SHOW COLUMNS FROM tb1; //描述表tb1
INSERT tb1(username,salary) VALUES('John',4500.69); //单独给某些字段复制
mysql> CREATE TABLE tb2(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, //主键约束,自增,主键
-> username VARCHAR(20) NOT NULL
-> );
UNIQUE KEY
唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值NULL
每张表可以存在多个唯一约束
mysql> CREATE TABLE tb3(
-> id SMALLINT PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) UNIQUE KEY
-> );
DEFAULT 规定默认值
mysql> CREATE TABLE tb4(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> sex ENUM('1','2','3') DEFAULT '3' //ENUM() 1,2,3里选一个值
-> );
FOREIGN KEYp
外键约束
保持数据一致性,完整性。
实现一对多或一对一的关系。
FOREIGN KEY 物理外键约束如CASCADE,SET NULL,RESTRICT,NO ACTION只能在INNODB数据库引擎中使用,在项目开发中多用逻辑约束。
(主键自动创建索引)
题外话:例如 SHOW CREATE TABLE table_name。展示的是表格形式若:SHOW CREATE TABLE table_name\G,则为另一种形式展现信息(\G后面不能加;)。
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name] //修改表名称 after是规定在哪个字段之后,若不写则在所有字段最下面
ALTER TABLE tbl_name DROP[COLUMN]col_name //删除列
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; //举个栗子(添加)
ALTER TABLE users1 ADD(col_name col_definition,...) //添加多列
ALTER TABLE users1 ADD qq varchar(10),DROP password; //同时添加和删除,同时删除、添加多个也同理
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id); //添加主键约束 constraint用来给约束起名字
ALTER TABLE users2 ADD UNIQUE (username); //添加唯一约束
ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces (id); //添加外键约束
ALTER TABLE users2 ALTER age SET DEFAULT 15; //添加默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT; //删除默认约束
ALTER TABLE users2 DROP PRIMARY KEY; //删除主键约束
ALTER TABLE users2 DROP INDEX username; //删除username的唯一约束 “INDEX”为唯一约束的名字
ALTER TABLE users2 DROP FOREIGN KEY 外键名字; //删除外键约束 外键名字通过SHOW CREATE TABLE tbl_name里面的constraint位置来查看
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name] //红字部分均可被修改
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL; //“CHANGE”修改,“CHANGE”功能范围大于“MODIFY”
INSERT专题
第一种插入方法 当表里的id为自增主键时,插入要用这样两种方法插入
INSERT INTO tbl_name VALUES(NULL,'TOM',25); //第一个字段为自增,所以设置为NULL让其自增
INSERT INTO tbl_name VALUES(DEFAULT,'TOM',52); //第一个字段为自增,所以设置为DEFAULT让其自增
INSERT INTO tbl_name VALUES(DEFAULT,'TOM',DEFAULT); //第三个字段设置表时存在默认值,故可以这样直接插入
INSERT INTO tbname VALUES(DEFAULT,'TOM',10*5-2),(DEFAULT,'JACK',md5('123')); //插入多个值;md5()为php计算哈希值的函数
第二种插入方法 与第一种的区别在于此方法可以使用子查询(SubQuery)
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...
第三种插入方法 此方法可直接将查询结果插入到指定数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT...
UPDATE
eg:UPDATE users SET age=age+1 WHERE id%2=0; //id为偶数的年龄加1岁
DELETE
DELETE FROM tbl_name [WHERE where_condition]
SELECT的简单介绍
多表链接后具有相同的字段,就这样用
SELECT users.name,users.id FROM users;
SELECT id AS userId,username AS uname FROM users;//起别名
SELECT查询的顺序将影响结果集的顺序;SELECT的别名将影响结果集的别名
GROUP BY分组
SELECT sex FROM users BROUP BY sex; //对性别进行分组 会分成男/女/NULL
SELECT age FROM users GROUP BY 1 HAVING age>20; //设定了条件后进行分组
SELECT COUNT(age) FROM users GROUP BY age HAVING age=20; //查找有多少个年龄=20岁的
//HAVING后面必须是:1.一个聚合函数(cout(),min(),max(),sum(),avg(),round(obj,2)[对obj的值四舍五入小数点后两位]等。如:SELECT avg(age) FROM users;2.规定条件的字段必须出现在select后面查找的字段中
//补充:聚合函数count()的用法
SELECT count(*) FROM users;//查询users表中的行数 包括NULL值
SELECT count(sex) FROM users;//查询users表中sex的行数,不包括NULL值,NULL值不算在行数内
ORDER BY排序
SELECT *FROM users ORDER BY id DESC; //DESC为降序排列,ASC为升序(默认)
SELECT *FROM users ORDER BY age,id DESC //age升序排列同时id降序排列
LIMT 限制
SELECT *FROM users LIMIT 2; //显示数据表的前两行数据
SELECT *FROM users LIMIT 2,2 //显示数据表的第三四行数据,因为第一个2是控制从哪一行开始,第二个2是控制显示几行,又因为数据表是从0开始计算的,所以2代表第三行
select emp_no, salary from salaries where salary = (select salary from salaries group by salary order by salary desc limit 1,1)//order by 后接 limit要注意重复数据的问题,所以最好用这种方式而不是直接order by a limit 1,1
子查询
SELECT AVG(goods_price) FROM tdb_goods; //计算平均值得出5636.36
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5636.36;//条件下计算
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods); //这就是子查询,将两个语句整合在一起。
当子查询返回多个值的时候需要条件判断(any,some,all)写在判断符号后面
如果一张表里面相同字段有相同名称的内容,容易造成查询时间长,所以需要用到外键(实施外键,比物理外键常用)
由于tdb_goods中的货物类型重复较多,且汉字字符串较长,会导致查询变慢,所以将其变成数字来加快查询速度,而这就需要使用另外一张货物类型表的cate_id来进行比对参照,就需要将两张表进行连接
创建tdb_goods_cate后:当需要将查询的结果输入到另一张表时,若得到结果过多,需要使用INSERT...SELECT将查询结果写入数据表,如下
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
UPDATE tdb_goods INNER JOIN tdb_goods_cate ON goods_cate=cate_name SET goods_cate=cate_id;
实现以上目的可用如下方法进行,仅需两步就可以执行
mysql> CREATE TABLE tdb_goods_brands(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL)
-> SELECT brand_name FROM tdb_goods GROUP BY brand_name;//通过SELECT实现在建表时直接插入数据
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS q ON g.brand_name=q.brand_name SET g.brand_name=q.brand_id;//两个表中出现同名字段,故用AS起别名后再进行。
内连接 只显示符合条件的部分
SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id; //好好分析这段代码
左外连接 显示左表全部和右表符合条件的
SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id; //好好分析这段代码
连接三张表
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id INNER JOIN tdb_goods_brands AS q ON g.brand_id=q.brand_id;
无限级分类表设计 有时功能往往需求多级分类,但写很多表会很麻烦 就需要自身连接 想象成两个相同的表,一个父表一个子表
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id=p.parent_id; //通过这段代码将更好展示出所属关系 (结合课程截图)
今天在做题的时候遇到了个小问题,就是date类型的数据,在插入的时候要加单引号
WHERE后面不能用聚合函数