MySQL 基础知识点回顾

MySQL基础 

登录	mysql -uroot -p  -h127.0.0.1 -P3306
创建数据库	CREATE DATABASES mydb;
显示数据库列表	SHOW DATABASES;（客户端显示中文乱码时输入：set character_set_results=gb2312;）
打开数据库	USE mydb;
显示数据表列表	SHOW TABLES;
显示数据表结构	DESC course;  SHOW COLUMNS FROM course;
创建表	CREATE TABLE xs(xh varchar(5),xm varchar(8),xb varchar(2));
修改mysql提示符	连上客户端之后：prompt 提示符
其他SHOW语句	SHOW CREATE DATABASE和SHOW CREATE TABLE： 分别用来显示创建特定数据库或表的MySQL语句； SHOW GRANTS ： 用来显示授予用户（所有用户或特定用户）的安全权限； SHOW STATUS ： 用于显示广泛的服务器状态信息；

查询1：

SEKECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY...LIMIT...

• SELECT：要返回的列或表达式
• FROM：从中检索数据的表
• WHERE：行级过滤
• GROUP BY：分组说明
• HAVING：组级过滤
• ORDER BY：输出排序顺序
• LIMIT：要检索的行数

检索不同的行	SELECT DISTINCT vend_id FROM products;
限制结果	SELECT prod_name FROM products LIMIT 5;            SELECT prod_name FROM products LIMIT 5,5;      (LIMIT 5, 5 指示MySQL返回从行5开始的5行。第一个参数为开始位置，第二个参数为要检索的行数。 LIMIT 1, 1将检索出第二行而不是第一行。)
数据排序（单列）	SELECT prod_name FROM products ORDER BY prod_name DESC;(默认是ASC)
数据排列（多列）	SELECT prod_id, prod_price，prod_name FROM products ORDER BY  prod_price，prod_name; (仅在多个行具有相同的 prod_price值时才对产品按 prod_name 进行排序。如果使用LIMIT ，它必须位于ORDER BY之后。）
过滤数据	SELECT prod_name, prod_price FROM products WHERE prod_price  between 5 and 10; (ORDER BY 位于 WHERE 之后)
创建分组	SELECT vend_id，COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=3; SELECT vend_id，COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP HAVING COUNT(*)>=3; (在having前加上WITH ROLLUP可显示汇总值) HAVING 和 WHERE 的差别： WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。
空值检查	SELECT prod_name FROM products WHERE prod_price IS NULL; 在通过过滤选择出不具有特定值的行时，不会返回具有NULL值的行。
IN操作符	SELECT employeeid, lastname, salary FROM employee_info WHERE lastname （NOT） IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');
匹配	SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%'; %：任意字符出现任意次数（0个，1个或多个） _：总是匹配一个字符 通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。不要过度使用通配符。
正则表达式	SELECT pro_name FROM products WHERE pro_name REGEXP '.000';  正则表达式可匹配1000及2000。 MySQL中的正则表达式不区分大小写。若要区分大小写可使用where pro_name REGEXP BINARY 'JetPack .000'; 查找特定字符时加\\  匹配\时则为'\\\'。为了匹配特殊字符，必须用\\为前导。\\-表示查找- ，\\. 表示查找 . 补充：一般在sql中可测试正则，可用：select 'hello' regexp '[0-9]'
文本处理	1.拼接函数：    SELECT CONCAT(vend_name, ' （', vend_country ,'）'）  FROM vendors ORDER BY vend_name； 　SELECT CONCAT_WS('-','A','B','C');        A-B-C 2.RTrim() 函数去掉值右边的所有空格。LTrim() 去掉串左边的空格。Trim() 去掉串左右两边的空格。 　SELECT TRIM(LEADING '??' FROM '??MySQL??');         MySQL??     前导 　SELECT TRIM(TRAILING '??' FROM '??MySQL??');        ??MySQL    后续 　SELECT TRIM(BOTH '??' FROM '??MySQL??');              MySQL 3.SELECT LEFT('MySQL',2);     My 4.SELECT REPLACE('??My??SQL??','?','');    MySQL 5.SELECT SUBSTRING('MySQL',1,2);    My       SELECT SUBSTRING('MySQL',2);       ySQL    SELECT SUBSTRING('MySQL',-2);     QL    (注意与Java中String的substring方法的区别)
日期时间处理	1.Date()函数可以将DateTime类型转化为Date类型，此外还有Time()函数。 2.定义一个日期范围：   SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2016-07-01' AND '2016-07-31';    SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2016 AND Month(order_date) = 7; 3.SELECT DATE_ADD(NOW(),INTERVAL 30 day); 　SELECT DATE_ADD(NOW(),INTERVAL -30 day); 4.SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31'); -31 5.SELECT DATE_FORMAT(NOW(),'%m/%d/%Y'); 07/06/2016
数值计算函数	SELECT Ceil(1.0001);    2    SELECT Floor(1.8888);  1    SELECT 3 DIV 4;            整数除法 0    SELECT 3/4;                  0.7500    SELECT 5.3 MOD 3;       2.3    SELECT Power(3,4);      81    SELECT Round(3.456789,4);         四舍五入  3.4568    SELECT Truncate(3.456789,4);      截断 3.4567
聚集函数	聚集函数（aggregate function）运行在行组上，计算和返回单个值的函数。 AVG() 某列平均值；（忽略列值为 NULL 的行。） COUNT() 某列行数； MAX() 某列最大值； MIN() 某列最小值； SUM() 某列值之和； 补充： 如果指定列名，则指定列的值为空的行被COUNT函数忽略，但如果COUNT函数中用的是星号（*），则不忽略。 与DISTINCT相结合：SELECT count( distinct userid ) from assess; （DISTINCT不能用于 COUNT(*)，需要指定列名。） 可与ROUND函数搭配使用：SELECT ROUND(AVG(price),2) FROM goods;

插入：

若某一列为auto_increment，则赋值时可以用NULL或者DEFAULT，其自动为1,2…;

若想插入某列默认值，可以直接用插入DEFAULT

INSERT INTO users VALUES(NULL,'a',12);

INSERT INTO users VALUES(NULL,'a',DEFAULT);

可直接插入多个值：INSERT INTO users VALUES(NULL,'a',12),(x,x,x),(y,y,y);  (MySQL用单条INSERT语句处理多个插入比使用多条 INSERT语句快。)

INSERT INTO users SET username='a',age=13;   此方法可以使用子查询，但只能每次插一条。

INSERT INTO Table2(field1,field2,...) SELECT value1,value2,... FROM Table1

在 INSERT 和SELECT 语句中不一定要求列名匹配。SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一个列， 第二列将用来填充表列中指定的第二个列，如此等等。

提高整体性能：数据检索若是最重要的，则可降低insert的优先级：insert low_priority into

使用last_insert_id()函数可以返回最后一个AUTO_INCREMENT值

更新：

UPDATE users SET age=age+8 WHERE id%2=0;

UPDATE users SET age=age-id,sex=0;

UPDATE users SET email=NULL WHERE …;

update时若发生错误则会恢复到原来的值，若想要在发生错误时继续更新，可以使用ignore关键字：update ignore customers …

删除：

若想要从表中删除所有行，可使用TRUNCATE TABLE tb3;

若不想更新和删除每一行，则delete和update子句必须带where子句，务必十分小心，Mysql中没有撤销。一般update和delete时应先用selete进行测试。

修改表：

修改表名	ALTER TABLE tb RENAME test1;   RENAME TABLE tb TO test1;
添加表列	ALTER TABLE tb ADD [column] name varchar(10) [FIRST | AFTER col_name];
删除表列	ALTER TABLE tb DROP [column] name;
修改表列	ALTER TABLE tb CHANGE column address address1 varchar(30)  --CHANGE多用于修改列名 若想要更改列的类型， CHANGE语法仍然要求旧的和新的列名称相同： ALTER TABLE tb CHANGE column address address varchar(20) ; ALTER TABLE tb MODIFY address char(10);   　　　　  --MODIFY 多用于修改表列属性 ALTER TABLE tb ALTER age SET DEFAULT 15; 　　　　  --设置默认值 ALTER TABLE tb ALTER age DROP DEFAULT;  　　　　　--删除默认约束