mysql-常用语句笔记

yangdong

文字版：

[SQL] 纯文本查看 复制代码

1，基本操作
Mysql -u root -p
show databases;    
Create database db_test;
use db_test; 
Show tables;
create table  bookinfo(
id int auto_increment primary key,
    bookname varchar(50) not null,
    price decimal  not null,
publishtime datetime,
    kind varchar(6)
    );
show columns from bookinfo;
alter table bookinfo modify column publishtime date;  //修改publishtime的类型
 
insert into bookinfo(bookname,price,publishtime,kind) values ('firstbook','50','2017-08-19','php');
 select * from bookinfo order by id;; 
select * from bookinfo limit 0,5;   //用于分页  显示从0到5个数据
select COUNT(*) AS total from bookinfo； //查找bookinfo的总行数返回以total为列名的数值
//特殊类操作   从tp_user_friend表中查询user_id相等字段，如果user_id相等就返回friend_id，反之也成立  IF(expr1 , expr2 , expr3)

如果expr1是true，则if()的返回值为expr2；否则返回值为expr3。 
SELECT
IF
( user_id = '.$user_id.', friend_id, user_id )          #相当于三元表达式
FROM
tp_user_friend 
WHERE
( user_id = '.$user_id.' OR friend_id = '.$user_id.') AND state = 1 
 
2，改变MySQL数据库的默认编码
首先修改MySQL的配置文件
在[client]下追加：
default-character-set=utf8
在[mysqld]下追加：
character-set-server=utf8
在[mysql]下追加：
default-character-set=utf8
修改完毕后，使用如下命令之一重启mysql服务：
改变后，之前手工创建的数据库如未显式指定编码，则其编码仍是默认的latin1，可使用如下命令更改编码：
mysql> alter database db_name CHARACTER SET utf8;
创建数据库就是用utf8
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 
创建表时就使用utf8
create table entries2 (
   id     int auto_increment 
) character set = utf8;
//每次创建表都加 character set = utf8
修改已有table的编码
alter table table_name convert to character set utf8;
查看表的编码方式
Show create table/database + table_name/database_name;
查看数据库的设置编码方式
show variables like '%char%'；
 
3，Mysql的优化
1，尽量选取最适合用的字段属性（能用Char（6）就不用Char（255））
DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
 
 
2，使用join来代替子查询
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)   ↓
 
SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID IS NULL
 
3，使用Union联合来代替手动创建的临时表
SELECT Name,Phone FROM client UNION SELECT Name,BirthDate FROM author UNION SELECTName,Supplier FROM product
 
4，事务（以Begin开头，Commit结束，保证数据同时插在多个关联表中，没有失败的插入）   有局限性少数用户
BEGIN; 
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11 WHERE item='book';
COMMIT;
 
5，锁定表（可以维护数据的完整性，但是它却不能保证数据的关联性）
LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
...
UPDATE inventory SET Quantity=11 WHERE Item='book';
UNLOCKTABLES
 
6，使用外键（如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。TYPE=INNODB
  ON DELETE CASCADE 保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除 ）
CREATE TABLE customerinfo(
 CustomerID INT NOT NULL,PRIMARY KEY(CustomerID)
)TYPE=INNODB;
 
CREATETABLE salesinfo(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID,SalesID),
           FOREIGN KEY(CustomerID) References customerinfo(CustomerID) ON DELETE CASCADE
)TYPE=INNODB;
 
7，创建索引（大量重复的值不要创建索引）
方法一：Create不能创建Primary key索引
CREATE UNIQUE INDEX / INDEX 索引名 ON 表名 (列名)
方法二：
ALTER TABLE table_name / 多个表  ADD INDEX / 唯一值不能重复的索引（ UNIQUE INDEX / PRIMARY KEY） index_name (column_list)
删除索引
DROP INDEX index_name ON talbe_name  =  ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY （一个表中只能有一个Primary Key索引，可以有多个Unique索引）
 
8，优化查询语句
1、首先，最好是在相同类型的字段间进行比较的操作。
2、在建有索引的字段上尽量不要使用函数进行操作。
3、在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
例如下面的查询将会比较表中的每一条记录。
SELECT * FROM books WHERE name like "MySQL%"
但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：
SELECT * FROM books WHERE name ＞= "MySQL" andname ＜ "MySQM"
4、应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。