注意:sql语句顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
一、基本命令
连接mysql :mysql -u name -p password -h host -P port;
显示数据库:show databases;
显示表:show tables;
显示表结构:describe 表名; show columns from 表名;
显示服务器信息:show status;
显示创建数据库的语句:show create database 数据库名;
显示创建表的语句:show create table 表名;
显示授予的权限:show grants;
显示错误和警告:show errors; show warnings;
二、基本查询
去重DISTINCT
SELECT DISTINCT vend_id
FROM products;
限制条数
从第一条起,取二条,第一条是从零开始
SELECT *
FROM products LIMIT 1,2;
从第一条起,取两条条,
SELECT *
FROM products LIMIT 2 OFFSET 1;
排序
倒序
SELECT vend_id
FROM products ORDER BY vend_id DESC;
多列排序
SELECT vend_id,prod_price
FROM products
ORDER BY vend_id DESC,prod_price DESC;
价格中最高:
SELECT vend_id,prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
条件查询
价格在10到40之间
SELECT vend_id,prod_price
FROM products
WHERE prod_price BETWEEN 10 AND 40;
邮件地址为空
SELECT *
FROM customers
where cust_email is NOT NULL;
显示价格大于10元且由1002或1003制造,and 高于 or
SELECT *
FROM products
where prod_price >= 10
and (vend_id = 1002 or vend_id = 1003);
in操作符和or相同
SELECT *
FROM products
where vend_id IN (1002,1003);
Like关键字
%表示多个字符
SELECT *
FROM products
where prod_name LIKE 'j%';
*一个字符
SELECT *
FROM products
where prod_name LIKE 'j_';
正则表达式(mysql仅支持正则表达式一小部分)
*匹配任意字符
SELECT *
FROM products
where prod_name REGEXP '.000';
BINARY 区分大小写
SELECT *
FROM products
where prod_name REGEXP BINARY 'j.';
匹配其中一个(相当于or)
SELECT *
FROM products
where prod_name REGEXP '1000|2000';
匹配组合字符(如:1 ton , 2 ton)
SELECT *
FROM products
where prod_name REGEXP '[123] ton';
也可以这样写:
SELECT *
FROM products
where prod_name REGEXP '[1|2|3] ton';
-匹配范围
SELECT *
FROM products
where prod_name REGEXP '[1-5] ton';
\\特殊字符
SELECT *
FROM products
where prod_name REGEXP '\\.';
匹配数字,至少出现了4次
SELECT *
FROM products
where prod_name REGEXP '[0-9]{4,}';
测试正则表达式(匹配成功返回1,否则返回0)
SELECT '12a' REGEXP '[0-9]{3,}';
字段拼接(多数系统使用+,||)但mysql使用concat()函数
SELECT CONCAT('产品名称:',prod_name,' 价格:',prod_price)
FROM products;
别名(可以不加单引号)
SELECT CONCAT('产品名称:',prod_name,' 价格:',prod_price)
as '详情'
FROM products;
按照日期查询
SELECT *
FROM orders
WHERE DATE(order_date) = '2005-09-1';
查询2005年9月份的订单
SELECT *
FROM orders
WHERE Year(order_date) = 2005
and Month(order_date) = 9;
组合聚集函数
SELECT COUNT(*) as num_items,
MAX(prod_price) as max_price,
MIN(prod_price) as min_price,
AVG(prod_price) as age_price
FROM products;
group by 分组
每个厂的产品个数
SELECT vend_id,count(*) as items_num
FROM products GROUP BY vend_id;
having 对分组后的数组过滤
SELECT vend_id,count(*) as items_num
FROM products GROUP BY vend_id
HAVING items_num >= 3;
inner join内连接
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
on vendors.vend_id = products.vend_id;
自连接
SELECT p1.prod_id,p2.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'fb';
外连接:
对每个客户所下订单统计(没有包括未下订单的)
SELECT customers.cust_id,COUNT(*)
FROM customers,orders
WHERE customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
也可以这样写:
SELECT customers.cust_id,COUNT(*)
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
包括未下订单的用户:
SELECT customers.cust_id,COUNT(orders.cust_id)
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
UNION去除多条select语句重复行
SELECT vend_id,prod_id,prod_price
FROM products
where prod_price<=5;
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id in(1002,1003);
三、全文索引
myISAM和InnoDB 前者支持
在创建表时,用FULLTEXT(被所引的字段),并制定表的存储引擎为MyISAM
CREATE TABLE fulltest(
uid int NOT NULL,
uname VARCHAR(20) NOT NULL,
info text NULL,
PRIMARY KEY(uid),
FULLTEXT(info)
)ENGINE=MyISAM;
全文索引(在where语句中)
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('Customer');
全文所引(在select语句中)
SELECT note_text,
MATCH(note_text) AGAINST('rabbit') AS rank;
FROM productnotes;
四、插入数据
注意:insert语句有可能很耗时(特别是更新很多索引时)用该语句INSERT LOW_PRIORITY INTO降低对数据库的影响
INSERT INTO student(uid,uname) value(12,'lisi');
INSERT INTO student value(12,'lisi');
INSERT INTO student1 SELECT * from student;
创建表时导入数据
CREATE table tt2(
uid INT NOT NULL,
uname VARCHAR(20) NOT NULL
) SELECT * FROM tt
五、更新数据
注意:更新数据时,一定不要忘了where条件
UPDATE tt
SET uid = 123,uname = 'lisi'
WHERE uid = 1;
六、删除数据
DELETE FROM tt
WHERE uid = 123;
七、表操作
1、创建表
CREATE TABLE IF NOT EXISTS test(
uid INT NOT NULL AUTO_INCREMENT,
uname VARCHAR(20) NOT NULL,
sex VARCHAR(2) NOT NULL DEFAULT '男',
PRIMARY KEY(uid)
) ENGINE=INNODB;
主键(primary key):非空且唯一
当使用了自动增量,可以使用SELECT LAST_INSERT_ID();查看下一条数据的自动增量值
alter修改表结构
(1)添加主键
alter table 表名 add constraint 主键 (形如:PK_表名) primary key 表名(主键字段);
(2)添加外键约束:
alter table 从表 add constraint 外键(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
(3)删除主键约束:(mysql)
alter table 表名 drop primary key;
(4)删除外键约束:
alter table 表名 drop foreign key 外键(区分大小写);
(5)添加列:
alter table 表名 add column 列名 varchar(30);
(6)删除列:
alter table 表名 drop column 列名;
(7)修改列名MySQL:
alter table bbb change nnnnn hh int;
(3)修改列属性:
alter table t_book modify name varchar(22);
删除表
DROP TABLE 表名;
重命名表
RENAME TABLE test TO test1
tt TO t1;
八、视图
使用总结:
使用CREATE VIEW 创建视图,
SHOW CREATE VIEW viewname;查看视图
DROP VIEW viewname; 删除视图
更新视图,可以先删除,也可以CREATE OR REPLACE VIEW
创建
CREATE VIEW testview AS
SELECT uid,uname
FROM test1;
使用视图
SELECT *
FROM testview;
九、存储过程
显示存储过程:SHOW CREATE PROCEDURE searchName;
模糊列出:SHOW PROCEDURE STATUS LIKE '%max%';
删除:CREATE PROCEDURE IF EXISTS 名称;
注意:使用mysql命令行,需要更改分隔符,
DELIMITER /.
1、无参数存储过程
CREATE PROCEDURE insetpro()
BEGIN
INSERT INTO protest value(1);
END
2、求最大,最小,平均值,存储过程
CREATE PROCEDURE pro_max_min_avg(
OUT max DECIMAL(8,2),
OUT min DECIMAL(8,2),
OUT avg DECIMAL(8,2))
BEGIN
SELECT MAX(prod_price)
INTO max
FROM products;
SELECT MIN(prod_price)
INTO min
FROM products;
SELECT AVG(prod_price)
INTO avg
FROM products;
END
调用:
CALL pro_max_min_avg(@max,@min,@avg);
SELECT @max,@min,@avg;
3、输入用户id返回用户姓名
CREATE PROCEDURE searchName(
IN uid INT,
OUT uname VARCHAR(20))
BEGIN
SELECT cust_name
FROM customers
WHERE cust_id = uid
INTO uname;
END
调用:
CALL searchName(10001,@uname);
SELECT @uname;
十、游标
使用流程:
定义游标-》打开游标-》操作游标-》关闭游标
(1)定义游标:
DECLARE cursor_name CURSOR FOR SELECT_statement;
(2)打开游标:
OPEN 打开游标
OPEN cursor_name;
(3)操作游标:
FETCH 获取游标当前指针的记录,并传给指定变量列表,注意变量数必须与MySQL游标返回的字段数一致,要获得多行数据,使用循环语句去执行FETCH
FETCH cursor_name INTO variable list;
(4)关闭游标:
CLOSE关闭游标
CLOSE cursor_name ;
注意:MySQL的游标是向前只读的,也就是说,你只能顺序地从开始往后读取结果集,不能从后往前,也不能直接跳到中间的记录
实例1:取一行插入到制定表中
CREATE PROCEDURE curdemo()
BEGIN
DECLARE a INT;
DECLARE b VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT uid,uname FROM tt2;
OPEN cur1;
FETCH cur1 INTO a, b;
INSERT INTO tt(uid,uname) value(a,b);
CLOSE cur1;
END
调用:
CALL curdemo();
实例2:将商品价格低于20的商品存储到ttt表中
CREATE PROCEDURE curtest()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE pid CHAR(10);
DECLARE pprice DECIMAL(8,2);
DECLARE curs CURSOR FOR SELECT prod_id,prod_price from products WHERE prod_price < 20;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN curs;
REPEAT
FETCH curs INTO pid,pprice;
INSERT INTO ttt value(pid,pprice);
UNTIL done END REPEAT;
CLOSE curs;
END
调用:
CALL curtest();
十一、触发器
创建触发器,需要这4条信息:
唯一的触发器名
触发器关联的表
触发器相应的活动(DELETE,INSERT,UPDATE)
触发器何时执行(之前,之后)
注意:mysql中,两个表可以有同名的触发器,只有表支持,临时表也不支持,每个表最多支持6个触发器,不能更新和覆盖,只能删除
1、创建触发器
向产品表插入一条数据,就将事件记录到log表中
CREATE TRIGGER tri1
AFTER INSERT ON products
FOR EACH ROW
BEGIN
INSERT INTO log VALUE('INSERT',NOW());
END
实例2:创建两个表,对t1插入的数据,自动插入到t2表
CREATE TABLE t1(
uid INT
);
CREATE TABLE t2(
uid INT
);
CREATE TRIGGER t12t2
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t2 value(NEW.uid);
END
测试:
INSERT INTO t1 VALUE(1);
INSERT INTO t1 VALUE(2);
INSERT INTO t1 VALUE(3);
INSERT INTO t1 VALUE(4);
SELECT * FROM t2;
输出:
1
2
3
4
实例3:t1表删除一条数据,将该条数据保存到t3表
CREATE TABLE t3(
uid INT
);
CREATE TRIGGER t1delt3
BEFORE DELETE ON t1
FOR EACH ROW
BEGIN
INSERT INTO t3 VALUE(OLD.uid);
END
测试:
DELETE FROM t1 WHERE uid=3;
SELECT * FROM t3;
输出:
3
实例4:更新t1表内容时,将更改前后数据写到t5表中
CREATE TABLE t5(
oid INT,
nid INT
);
CREATE TRIGGER t1updatet5
BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
INSERT INTO t5 VALUE(OLD.uid,NEW.uid);
END
t1表测试:
UPDATE t1 SET uid = 3 WHERE uid = 4;
SELECT * FROM t1;
t1表输出:
1
3
10
t5表测试:
SELECT * FROM t5;
t5表输出:
4 3
可以看出,这个表记录上上次修改的值
总结:
OLD.col_name在更新或删除它之前,引用已有行中的1列。
NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。
十二、事务
MyISAM和InnoDB,前者不支持事务
术语:
事务(transaction):一组sql语句
回退(rollback):撤销制定sql语句的过程
提交(commit):将未存储的sql语句写入数据库
保留点(savepoint)相当于一个标记,你可以回退到某个点
1、使用事务
SELECT * FROM orderitems;
START TRANSACTION;
DELETE FROM orderitems;
SELECT * FROM orderitems;
ROLLBACK;
SELECT * FROM orderitems;
可回退的语句:
Insert,delete,update,在事务中可以包含,create ,drop ,但就算事务回退,他们也不会回退
2、保存点
SAVEPOINT P1;
ROLLBACK TO p1;
保存点在事务完成时(rollback,commit)后自动释放,mysql 5以来,可以显示释放RELEASE SAVEPOINT;
3、设置自动提交,针对某个连接
SET autocommit=0;
十二、字符集和编码
字符集:字母和符号的集合
编码:某个字符集陈成员的内部表示
校对:字符如何比较
查看mysql 支持的字符集和校对
SHOW CHARACTER SET;
SHOW COLLATION;
_cs 区分大小写
_ci 不区分大小写
2、查看该系统所用字符集和校对
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
3、创建表指定
CREATE TABLE mytable(
col1 INT,
col2 INT
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
4、创建表时,指定某列字符和校对
CREATE TABLE mytable1(
col1 INT,
col2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
5、在查询时指定校对,还应用于GROUP BY,HAVING,聚集函数,别名
创建表:
CREATE TABLE t7(
uname VARCHAR(20)
)DEFAULT CHARACTER SET latin1
COLLATE latin1_general_ci;
查询:
SELECT * FROM t7 ORDER BY uname COLLATE latin1_general_cs;
十三、安全
1、用户管理
查看所有用户:SELECT user FROM user;
创建用户:CREATE USER u1 IDENTIFIED BY 'u1';
CREATE USER 'u6'@'localhost' IDENTIFIED by 'u6';
grant all privileges on test.* to 'u3'@'localhost';
FLUSH PRIVILEGES;
不指定主机名的用户只能远程访问
重命名:RENAME USER u1 TO u2;
删除:DROP USER u2;
2、权限
查看:SHOW GRANTS FOR u1;
授权:GRANT SELECT,UPDATE ON exam.* to u1;
撤销:REVOKE SELECT,UPDATE ON exam.* FROM u5;
3、控制权限层次
整个服务器:GRANT ALL,REVOKE ALL;
整个数据库:ON database.*;
特定表:ON database.tablename;
特定列:
特定存储过程
4、更改密码
SET PASSWORD FOR u3@localhost = PASSWORD('u3');
更改当前登录用户:SET PASSWORD = PASSWORD('root');
十四、数据库管理
1、备份
myaqldump
mysqlhotcopy 有些引擎可能不支持
BACKUP TABLE 或 SELECT INTO OUTFILE 用RESTORE TAB;E还原
在备份之前,应刷新数据,FLUSH TABBLES;
整理于2013-8-19 by renpingqing