运行环境：windows 7； mysql5.5.48；
数据库操作：
登录：
C:\Windows\system32>mysql -uroot -pxxxx """xxxx是你的密码 """打开数据库可用：
USE 数据库名字;创建数据库：
CREATE ｛DATABASE | SCHEMA｝ [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;e.g:
mysql>CREATE DATABASE db1;如果加IF NOT EXISTS，会忽略报错，显示警告，可以用以下显示警告信息：
SHOW WARNINGS;查看当前服务器下的数据库列表：
SHOW {DATABASES | SCHEMA} ；e.g:
SHOW DATABASES;修改数据库的默认编码方式：
ALTER {DATABASE | SCHEMA} 表名 [DEFAULT] CHARACTER SET [=] charset_name; e.g:
ALTER DATABASE db1 CHARACTER SET gbk;可以用以下查看创建数据库中的命令：
SHOW CREATE DATABASE db1;可以看到编码方式从utf-8改成了gbk;
删除数据库：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;总结增删改查：
增：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name ；
删：DROP DATABASE db_name;
改：ALTER DATABASE db_name CHARACTER SET charset_name;
查：显示所有的数据库 ：SHOW DATABASES;
数据库表操作：
1. 创建数据表：
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
.........
);e.g:
CREATE TABLE tb1(
username VARCHAR(20), """曾忘记写逗号"""
age TINYINT UNSIGNED, """曾写成UNSGINED"""
salary FLOAT(8,2) UNSIGNED """FLOAT这样表示为共8位，有2位小数"""
);2. 查看数据表列表：
SHOW TABLE [FROM db_name];"""如果加了FROM 可以查看其它数据库的表，没加是查看当前数据库的表"""查看数据表结构：
SHOW COLUMNS FROM tb_name;3. 修改数据表：
1. 添加单列：
ALTER TABLE table_name ADD column_name colunm_destriction [FIRST|AFTER column_name];e.g1(向数据表users添加age列):
ALTER TABLE users ADD age TINYNIT UNSIGNED NOT NULL DEFALUT 10;e.g2(想数据表users添加password列，且位于username之后）：
ALTER TABLE users ADD password VARCHAR(32) NOT NULL AFTER username;关于[FIRST | AFTER ] 的使用：
AFTER 新加入的列位于指定列的后方，FIRST 新加入的列加到最前面；如果不写AFTER 或 FIRST 则默认新加列位于所有列的后方。
2. 添加多列：
ALTER TABLE table_name ADD column_name colunm_destriction [FIRST|AFTER column_name];注意：添加多列要加括号，而且不能指定位置关系
3.删除列：
ALTER TABLE tb1_name DROP [COLUMN] col_namee.g(删除password与age列）：
ALTER TABLE users DROP password,DROP age;也可以同时添加删除列，就把DROP 换成ADD 即可，记得用逗号隔开。
4. 添加主键约束：
ALTER TABLE tbl_name ADD CONSTRAINT PK_uesrs_id PRIMARY KEY(id);加了CONSTRAINT就可以在后面起别名
3.数据表中的记录的操作：
记录的查找：
e.g:
SELECT * FROM tb1 ;记录的插入1/3：
INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...);"""其中如果省略col_name,则表示要在VALUES进行表中的所有列的赋值,如果有写列名，则只要在VALUSE中向相应列赋值即可"""先建一张表：
mysql> CREATE TABLE users (
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username varchar(20) not null,
-> password varchar(32) not null,
-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
-> sex BOOLEAN
-> );插入一个记录：
mysql> INSERT users VALUES(NULL,'TOM','123',25,1); """id 因为有自动编号属性，可以用null或default赋值，age列也可以用表达式比如：2*7-1结果就为13，也可以用default表示默认值；"""结果：
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | TOM | 123 | 25 | 1 |
+----+----------+----------+-----+------+
1 row in set (0.00 sec)可以同时插入两条记录，用逗号隔开就好了，原先有5行，这里准备插入6,7：
mysql> INSERT users VALUES(NULL,'JOHN','456',2*2+10,1),(NULL,'ROSE',md5('123'),
-> DEFAULT,1);结果：
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | TOM | 123 | 25 | 1 |
| 2 | JOHN | 456 | 25 | 1 |
| 3 | TOM | 123 | 25 | 1 |
| 4 | JOHN | 456 | 14 | 1 |
| 5 | TOM | 123 | 10 | 1 |
| 6 | JOHN | 456 | 14 | 1 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 10 | 1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)记录的插入2/3:
INSERT [INTO] tb1_name SET col_name={expr|DEFAULT},……说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery）;且只能一次性插入一条记录
例：INSERT users SET username=’Ben’,password=’456’;
结果：
mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | TOM | 123 | 25 | 1 |
| 2 | JOHN | 456 | 25 | 1 |
| 3 | TOM | 123 | 25 | 1 |
| 4 | JOHN | 456 | 14 | 1 |
| 5 | TOM | 123 | 10 | 1 |
| 6 | JOHN | 456 | 14 | 1 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 10 | 1 |
| 8 | Ben | 456 | 10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)记录的插入3/3：
待续记录的更新（单表更新）：
如果写完记录想更改，则需要update语句：
UPDATE table_name SET col_name1={expression|DEFAULT},[col_name2=(expression|DEFAULT)],..... [WHERE where_condition]；（如果不指定WHERE则全表更新）e.g:
mysql> UPDATE users SET age=age+5;结果：
mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | TOM | 123 | 30 | 1 |
| 2 | JOHN | 456 | 30 | 1 |
| 3 | TOM | 123 | 30 | 1 |
| 4 | JOHN | 456 | 19 | 1 |
| 5 | TOM | 123 | 15 | 1 |
| 6 | JOHN | 456 | 19 | 1 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 15 | 1 |
| 8 | Ben | 456 | 15 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)可以同时更新多列：
mysql> UPDATE users SET age=age-id,sex=0;结果：
mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | TOM | 123 | 29 | 0 |
| 2 | JOHN | 456 | 28 | 0 |
| 3 | TOM | 123 | 27 | 0 |
| 4 | JOHN | 456 | 15 | 0 |
| 5 | TOM | 123 | 10 | 0 |
| 6 | JOHN | 456 | 13 | 0 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 7 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)带有where的修改（偶数id年龄加10）：
mysql> UPDATE users SET age=age+10 WHERE id%2=0;结果：
mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | TOM | 123 | 29 | 0 |
| 2 | JOHN | 456 | 38 | 0 |
| 3 | TOM | 123 | 27 | 0 |
| 4 | JOHN | 456 | 25 | 0 |
| 5 | TOM | 123 | 10 | 0 |
| 6 | JOHN | 456 | 23 | 0 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)记录的删除（单表删除）：
DELETE FROM table_name [WHERE where_condition] """不指定条件，则该表全部记录被删除，当删除某一条记录再添加一条新记录的时候，序号为原有最大序号+1"""e.g:
mysql> DELETE FROM users WHERE id=6;结果：
mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | TOM | 123 | 29 | 0 |
| 2 | JOHN | 456 | 38 | 0 |
| 3 | TOM | 123 | 27 | 0 |
| 4 | JOHN | 456 | 25 | 0 |
| 5 | TOM | 123 | 10 | 0 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)删除所有记录：
DELETE FROM users;删除一个表：
DROP TABLE users;查询表达式解析：
查询部分列：
mysql> select id,username from users;
+----+----------+
| id | username |
+----+----------+
| 1 | TOM |
| 2 | JOHN |
| 3 | TOM |
| 4 | JOHN |
| 5 | TOM |
| 7 | ROSE |
| 8 | Ben |
+----+----------+
7 rows in set (0.00 sec)用于区分多表联结时不同表存在相同字段：
mysql> SELECT users.id,users.username FROM users;
+----+----------+
| id | username |
+----+----------+
| 1 | TOM |
| 2 | JOHN |
| 3 | TOM |
| 4 | JOHN |
| 5 | TOM |
| 7 | ROSE |
| 8 | Ben |
+----+----------+
7 rows in set (0.00 sec)这个例子看不出作用，要多表才看出作用。
取别名：
mysql> SELECT id AS userid,username AS uname FROM users;
+--------+-------+
| userid | uname |
+--------+-------+
| 1 | TOM |
| 2 | JOHN |
| 3 | TOM |
| 4 | JOHN |
| 5 | TOM |
| 7 | ROSE |
| 8 | Ben |
+--------+-------+
7 rows in set (0.00 sec)查询结果分组：
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | TOM | 123 | 29 | 0 |
| 2 | JOHN | 456 | 38 | 0 |
| 3 | TOM | 123 | 27 | 0 |
| 4 | JOHN | 456 | 25 | 0 |
| 5 | TOM | 123 | 10 | 0 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 9 | JOHN | 123 | 23 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT sex FROM users GROUP BY sex;
+------+
| sex |
+------+
| NULL |
| 0 |
+------+查询结果排序：
mysql> SELECT * FROM users ORDER BY id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 9 | JOHN | 123 | 23 | NULL |
| 8 | Ben | 456 | 17 | 0 |
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 5 | TOM | 123 | 10 | 0 |
| 4 | JOHN | 456 | 25 | 0 |
| 3 | TOM | 123 | 27 | 0 |
| 2 | JOHN | 456 | 38 | 0 |
| 1 | TOM | 123 | 29 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)同时对两列进行排序：
mysql> SELECT * FROM users ORDER BY age,id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 7 | ROSE | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 5 | TOM | 123 | 10 | 0 |
| 8 | Ben | 456 | 17 | 0 |
| 9 | JOHN | 123 | 23 | NULL |
| 4 | JOHN | 456 | 25 | 0 |
| 3 | TOM | 123 | 29 | 0 |
| 1 | TOM | 123 | 29 | 0 |
| 2 | JOHN | 456 | 38 | 0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)限制查询结果返回数量：
mysql> select * from users limit 2,2;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 3 | TOM | 123 | 29 | 0 |
| 4 | JOHN | 456 | 25 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)注意这里返回的是id为3,4而不是2,3。因为limit后第一个参数是记录的相对位置，而记录是从0开始编号，跟id没有任何关系。
彩蛋，将一个表的查询结果写入另一个表中：
比如已新建了一个包含id,username列的test表，接下来将users表中的查询记录写入test空表中：
INSERT test(username) SELECT username FROM users WHERE age >= 30;此后查询test表发现test中被写入了记录。
记录的子查询：
小tips:
以网格形式呈现数据：
SELECT * FROM tdb_goods\G (注意不需要分号结尾)让客户端可以显示中文，但不影响服务器已录入的UTF8格式：
SET NAMES gbk;使用比较运算符的子查询：
e.g1查询商品价格大于平均价格的产品：
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price >=
（SELECT ROUND(AVG(goods_price),2）FROM tdb_goods;e.g2
查询商品价格大于商品范畴名称为超级本的最大价格的商品：
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price >=
ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本’);e.g3
由于tdb_goods表中的商品分类为中文存储，当表记录达到百万级以上时，查询变得非常慢，如果用数字编号代替中文描述，可以改善性能，接下来，将建立tdb_goods_cates表来存储中文分类：
3.1 创建表：
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40)
);3.2 将查询结果写入数据表：
INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;3.3 通过tdb_goods_cates数据表来更新tdb_goods表：
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
SET goods_cate = cate_id ;e.g4通过CREATE…SELECT来创建数据表并且同时写入记录（对e.g3的改进）：
4.1 创建tdb_goods_brands表，并把查询结果写入表中：
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;4.2 更新两表之间的关系（取了别名以免混淆不同表的相同字段名）：
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
SET g.brand_name = b.brand_id;4.3 通过ALTER TABLE语句修改数据表结构（查看原表，会发现brand_name还是字符类型，所以需要修改）：
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;关于连接：
先准备数据：
分别在tdb_goods_cates和tdb_goods_brands表插入记录：
INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');在tdb_goods数据表写入任意记录：
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');内连接：
查询所有商品的详细信息(通过内连接实现)：
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 b ON g.brand_id = b.brand_id\G左外连接：
查询所有商品的详细信息(通过左外连接实现)：
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G右外连接：
查询所有商品的详细信息(通过右外连接实现)：
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G无限分类:
先创建表：
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
插入记录：
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);查找所有分类及其父类：
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.type_id;查找所有分类及其子类:
SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
查找重复记录:
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;删除重复记录:
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
mysql变量的术语分类：
1.用户变量：以”@”开始，形式为”@变量名”
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.变量名
对所有客户端生效。只有具有super权限才可以设置全局变量
3.会话变量：只对连接的客户端有效。
4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量
扫一扫
520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
