Mysql必知必会笔记

最新推荐文章于 2024-01-24 18:18:13 发布

AIGeeker

最新推荐文章于 2024-01-24 18:18:13 发布

阅读量139

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/RufyHuang/article/details/108607517

版权

选择数据库

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');

AIGeeker

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫