- 选择数据库
USE 数据库名;
- 查看有哪些数据库
SHOW DATABASES;
- 查看数据库内的表的列表
SHOW TABLES;
- 查看表中的列的列表
1.SHOW COLUMNS FROM 表名;
2.DESCRIBE 表名;
- 用来显示授予用户(所有用户或特定用户)的安全权限
SHOW GRANTS;
- 查询第一行或前几行(查询指定几行)
//注意:有行0的存在
//查询前5行
SELECT *
FORM 表名
LIMIT 5;
//查询从第4行开始的5行记录
SELECT *
FROM 表名
LIMIT 4,5;
- 拼接Concat()
SELECT Concat(vend_name, '(',vend_country,')')
FROM vend_name;
- 去掉串空格Trim()
RTrim(vend_name) //去掉串右边空格
LTrim(vend_name) //去掉串左边空格
Trim(vend_name) //去掉串左右两边的空格
- 文本处理函数
Left() //返回串左边的字符
Right() //返回串右边的字符
Lower() //将串转换为小写
Upper() //将串转换为大写
Length() //返回串的长度
Locate() //找出串的一个字串
SubString() //返回字串的字符
Soundex() //返回串的SOUNDEX值 比较发音。
- 日期和时间处理函数
AddDate() //增加一个日期(天、周等)
AddTime() //增加一个时间(时、分等)
CurDate() //返回当前日期
CurTime() //返回当前时间
Date() //返回日期时间的日期部分
DateDiff() //计算两个日期之差
Date_Add() //高度灵活的日期运算函数
Date_Format() //返回一个格式化的日期或时间串
Day() //返回一个日期的天数部分
DayOfWeek() //对于一个日期,返回对应的星期几
Hour() //返回一个时间的小时部分
Minute() //返回一个日期的分钟部分
Month() //返回一个日期的月份部分
Now() //返回当前日期和时间
Second() //返回一个时间的秒部分
Time() //返回一个日期时间的时间部分
Year() //返回一个日期的年份部分
--检索2005年9月下的所有订单
SELECT order_id,order_num
FROM orders
WHERE Year(order_date)=2005 AND Month(order_date)=9;
- 常用的数值处理函数
Abs() //返回一个数的绝对值
Sqrt() //返回一个树的平方根
Mod() //返回除操作的余数
Rand() //返回一个随机数
Pi() //返回圆周率
Sin() //返回一个角度的正弦
Cos() //返回一个角度的余弦
Tan() //返回一个角度的正切
Exp() //返回一个数的指数值
- 分组小计——WITH ROLLUP关键字
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
- 过滤分组—HAVING
SELECT cust_id,COUNT(*) AS orders
FROM oders
HAVING COUNT(*)>=2;
- 使用全文本搜索
1.启用全文本搜索支持
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text) //启动全文本搜索
)ENGINE=MyISAM;
2.进行全文本搜索——Match()和Against()
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit'); //按照等级值返回结果
3.使用查询扩展
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
- 布尔文本搜索
1.全文本布尔操作符
布尔操作符 说明
+ 包含,词必须存在
- 排除,词必须不出现
> 包含,而且增加等级值
< 包含,且减少等级值
() 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~ 取消一个词的排序值
* 词尾的通配符
“ ” 定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)
2.操作符例子
//搜索匹配包含词rabbit和bait的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
//没有操作符,这个搜索匹配包含rabbit和bait中的至少一个词的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
//搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('“rabbit bait”' IN BOOLEAN MODE);
- 更快的删除表数据
TRUNCATE TABLE
- 获取最后一个AUTO_INCREMENT值
SELECT last_insert_id()
-
默认值关键字DEFAULT
-
ALTER TABLE定义外键
ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY(vend_id) REFERENCES vendors(vend_id);
- 重命名表
RENAME TABLE t1 TO t2,
t3 TO t4,
t5 TO t6;
- 命令行创建存储过程——DELIMITER //
DELIMITER // --意思是临时更改命令行实用程序的语句分隔符。
CREATE PROCEDURE productprice()
BEGIN
SEECT Avg(pro_price) AS priceaverage
FROM products;
END //
DELIMITER ; --恢复为原来的语句分隔符。
- 调用存储过程`
CALL productprice();
- 删除存储过程
1.DROP PROCEDURE productprice;
2.DROP PROCEDURE IF EXISTS productprice;
- 带参数存储过程
--创建带参数的存储过程
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2) --小数两位,总长度不超过8
)
BEGIN
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num=onumber
INTO ototal;
END;
--调用存储过程
CALL ordertotal(2005,@total);
--显示
SELECT @total;
- 检查存储过程
--显示用来创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE ordertotal;
--获得存储过程的详细信息等
SHOW PROCEDURE STATUS;
- 创建游标、打开关闭游标
CREATE PROCEDURE processorders()
BEGIN
--Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
--Open the cursor
OPEN ordernumbers;
--Close the cursor
CLOSE ordernumbers;
END;
- 使用存储过程,游标,逐行处理以及存储过程调用其他存储过程的样例
CREATE PROCEDURE processorders()
BEGIN
--Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
--Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT oder_num FROM orders;
--Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
--Create a table to store the results
CREATE TABLE IF NOT EXISTE ordertotals
(order_num INT,total DECIMAL(8,2));
--Open the cursor
OPEN ordernumbers;
--Loop through all rows
REPEAT
--Get order number
FETCH ordernumbers INTO o;
--Get the total for this order
CALL ordertotal(o,1,t);
--Insert order and total into ordertotals
INSERT INTO ordertotals(order_num,total)
VALUES(o,t);
--End of loop
UNTIL done END REPEAT;
--Close the cursor
CLOSE ordernumbers;
END;
- 创建INSERT触发器
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num; //返回新订单
- 创建DELETE触发器
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;
- 创建UPDATE触发器
--虚拟表OLD访问(UPDATE语句之前)的值。
--虚拟表NEW访问新更新的值
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);
- 使用字符集和校对顺序
--查看所支持的字符集完整列表
SHOW CHARACTER SET;
--查看所支持校对的完整列表
SHOW COLLATION;
--给表指定字符集和校对
CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
- 创建用户账号
CREATE USER ben IDENTIFIED BY '123'
--重命名用户账号
RENAME USER ben TO bforta;
--删除用户账号
DROP USER bforta;
- 查看用户账号的权限
SHOW GRANTS FOR ben;
- 设置权限GRANT/REVOKE
--赋予权限
GRANT SELECT ON databases.* TO ben;
--取消权限
REVOKE SELECT ON databases.* FROM ben;
- 权限列表
权限 说明
ALL 除GRANT OPTION外的所有权限
ALTER 使用ALTER TABLE
ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE
CREATE 使用CRATE TABLE
CREATE ROUTINE 使用CREATE PROCEDURE
CREATE TEMPORARY TABLES 使用CREATE TEMPORARY TABLE
CREATE USER 使用CREATE USER,DROP USER,RENAME USER和REVOKE ALL PRIVILEFTES
CREATE VIEW 使用CREATE VIEW
DELETE 使用DELETE
DROP 使用DROP TABLE
EXECUTE 使用CALL和存储过程
FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION 使用GRANT和REVOKE
INDEX 使用CREATE INDEX和DROP INDEX
INSERT 使用INSERT
LOCK TABLES 使用LOCK TABLES
PROCESS 使用SHOW FULL PROCESSLIST
RELOAD 使用FLUSH
REPLICATION CLIENT 服务器位置的访问
REPLICATION SLAVE 由复制从属使用
SELECT 使用SELECT
SHOW DATABASES 使用SHOW DATABASES
SHOW VIEW 使用SHOW CRATE VIEW
SHUTDOWN 使用mysqladmin shutdown(用来关闭mysql)
UPDATE 使用UPDATE
USAGE 无访问权限
- 更改口令
SET PASSWORD FOR ben=Password('123');
--为当前登陆用户设置口令
SET PASSWORD=Password('123');