MySQL关键字与语句

MySQL关键字与语句
增
-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name;
-- 创建表
CREATE TABLE IF NOT EXISTS `et_catalogmetadata`
-- 创建表
CREATE TABLE test(id int,body varchar(100));
-- 新建本地⽤户
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
-- 新建远程⽤户
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
-- 增加列
ALTER TABLE tasks ADD COLUMN complete DECIMAL(2,1) NULL AFTER description;
-- 数据库名所对应的数据库不存在的情况下创建.如果已经存在则不创建
CREATE DATABASE IF NOT EXISTS mydatabase
IF NOT EXISTS是语句的可选⼦句。

IF NOT EXISTS⼦句可防⽌创建数据库服务器中已存在的新数据库的错误。

不能在MySQL数据库服务器中具有相同名称的数据库。

该语句也可⽤于创建表
删
-- 删除⽤户
DROP USER 'test'@'localhost';
-- IF EXISTS是该语句的可选部分，以防⽌您删除数据库服务器中不存在的数据库。

DROP DATABASE [IF EXISTS] database_name;
-- 删除表
DROP TABLE table_name ;
-- 删除索引
drop index index_user_id on table_name;
-- 删除PRIMARY KEY索引时使⽤
ALTER TABLE table_name DROP PRIMARY KEY
改
-- 赋予insert,select,update权限和全部权限，test01=数据库，dev=⽤户：
GRANT INSERT, SELECT, UPDATE ON test01.* TO 'dev' @'%';
-- 不允许这个⽤户给其他⽤户授权
GRANT ALL PRIVILEGES ON test01.* TO 'dev' @'%';
-- 允许这个⽤户给其他⽤户授权
GRANT ALL PRIVILEGES ON.TO ‘ wangxiang’ @’ %’ WITH GRANT OPTION;
-- 刷新权限
FLUSH PRIVILEGES;
-- 收回权限
REVOKE ALL PRIVILEGES ON *.* FROM 'test'@'%';
-- 修改Root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
-- 指定密码认证插件
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassowrd';
-- 修改⽤户密码，两种⽅式
ALTER USER user IDENTIFIED BY 'auth_string';
update er set password='hiveuser' where user='hiveuser';
-- 修改密码验证插件
ALTER USER ‘nextcloud‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘new_password‘;
#开启远程访问
use mysql;
update user set host='%' where user ='root';
#
-- 重命名表
ALTER TABLE tasks
RENAME TO work_items;
-- 更新表数据，将email列的1056⾏修改为 mary.new@
UPDATE employees SET email = 'mary.new@' WHERE employeeNumber = 1056;
-- update配合select语句
UPDATE customers SET sales = (SELECT employee FROM employees WHERE jobtitle = 'Sales Rep' LIMIT 1) WHERE sales IS NULL;
查
-- 查看所有数据库
SHOW DATABASES;
-- 查看权限
SHOW GRANTS FOR 'test'@'%';
-- 查看所有表
SHOW TABLES;
-- 查找所有⽤户
USE db_name;
SELECT HOST, USER,PASSWORD FROM ER;
-- 查看表结构
DESC table_name;
-- 查看⽇志开启状态
SHOW VARIABLES LIKE 'log_%';
-- 查看所有binlog⽇志列表
SHOW MASTER LOGS;
-- 查看所有库的⼤⼩,务必先执⾏use
use information_schema;
SELECT concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS DATA FROM TABLES;
-- 查看指定库的指定表的⼤⼩
SELECT concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS DATA FROM TABLES WHERE table_schema = 'jishi' AND table_name = 'a_ya';
-- 查看指定库的指定表的索引⼤⼩
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Index_Size' FROM TABLES--WHERE table_schema = 'test' and table_name='a_yuser'; -- 查看索引
show index from tblname;
show keys from tblname;
参数说明
Non_unique如果索引不能包括重复词，则为0。

如果可以，则为1。

Key_name索引的名称。

Seq_in_index索引中的列序列号，从1开始。

Column_name列名称。

Collation列以什么⽅式存储在索引中。

在MySQL中，有值‘A’（升序）或NULL（⽆分类）。

Cardinality索引中唯⼀值的数⽬的估计值。

基数越⼤，当进⾏联合时,使⽤该索引的机会就越⼤。

Sub_part如果列只是被部分地编⼊索引，则为被编⼊索引的字符的数⽬。

如果整列被编⼊索引，则为NULL。

Packed指⽰关键字如何被压缩。

如果没有被压缩，则为NULL。

Null如果列含有NULL，则含有YES。

如果没有，则该列含有NO。

Index_type⽤过的索引⽅法（BTREE, FULLTEXT, HASH, RTREE）。

Comment计算某列某个字符出现的次数：
外键
以下语法说明了如何在语句中的⼦表中定义外键。

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
SQL
详细分析：
CONSTRAINT⼦句允许您为外键约束定义约束名称。

如果省略它，MySQL将⾃动⽣成⼀个名称。

FOREIGN KEY⼦句指定⼦表中引⽤⽗表中主键列的列。

您可以在FOREIGN KEY⼦句后放置⼀个外键名称，或者让MySQL为您创建⼀个名称。

请注意，MySQL会⾃动创建⼀个具有foreign_key_name名称的索引。

REFERENCES⼦句指定⽗表及其⼦表中列的引⽤。

在FOREIGN KEY和REFERENCES中指定的⼦表和⽗表中的列数必须相同。

ON DELETE⼦句允许定义当⽗表中的记录被删除时，⼦表的记录怎样执⾏操作。

如果省略ON DELETE⼦句并删除⽗表中的记录，则MySQL将拒绝删除⼦表中相关联的数据。

此外，MySQL还提供了⼀些操作，以便您可以使⽤其他选项，例如，当删除⽗表中的记录时，MySQL可以删除⼦表中引⽤⽗表中记录的记录。

如果您不希望删除⼦表中的相关记录，请改⽤ON DELETE SET NULL操作。

当⽗表中的记录被删除时，MySQL会将⼦表中的外键列值设置为NULL，条件是⼦表中的外键列必须接受NULL值。

请注意，如果使⽤ON DELETE NO ACTION或ON DELETE RESTRICT操作，MySQL将拒绝删除。

ON UPDATE⼦句允许指定在⽗表中的⾏更新时，⼦表中的⾏会怎样执⾏操作。

当⽗表中的⾏被更新时，可以省略ON UPDATE⼦句让MySQL拒绝对⼦表中的⾏的任何更新。

ON UPDATE CASCADE操作允许您执⾏交叉表更新，并且当更新⽗表中的⾏时，ON UPDATE SET NULL操作会将⼦表中⾏中的值重置为NULL值。

ON UPDATE NO ACTION或UPDATE RESTRICT操作拒绝任何更新。

添加外键
MySQL添加外键语法
要将外键添加到现有表中，请使⽤语句与上述外键定义语法：
ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;
删除外键
使⽤ALTER TABLE语句将外键删除，如下语句：
ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;
在上⾯的声明中：
⾸先，指定要从中删除外键的表名称。

其次，将约束名称放在DROP FOREIGN KEY⼦句之后。

SELECT
KEYY AS '0',
count(*) AS KEYY
FROM
t_dict
GROUP BY
KEYY
LIMIT 0,1
与其他表的某个字符进⾏对⽐，如果没找到，则输出该⾏：
SELECT NAME AS
Customers
FROM
Customers
LEFT JOIN Orders ON Orders.CustomerId = Customers.Id
WHERE
Orders.CustomerId IS NULL
两表⽐对，返回不相同数据
-- ⽅法1
SELECT * FROM t2 WHERE id NOT IN (SELECT id FROM t1);
-- ⽅法2
SELECT * FROM t2 WHERE NOT EXISTS(SELECT * FROM t1 WHERE t1.id=t2.id);
-- ⽅法3
SELECT t2.* FROM t2 LEFT JOIN t1 ON t2.id=t1.id WHERE t1.id IS NULL;
模糊查询统计
SELECT COUNT(1) AS create_time FROM vote_record_memory WHERE create_time LIKE '1980-02%';建表语句
CREATE TABLE massage (
id INT AUTO_INCREMENT COMMENT "主键",
NAME VARCHAR ( 10 ) NOT NULL,
ge INT ( 3 ) NOT NULL,
pos VARCHAR ( 10 ),
phone INT ( 11 ),
PRIMARY KEY ( id )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
找到不为空的记录
SELECT * FROM customers WHERE addressLine2 IS NOT NULL;
mysql中查看各表的⼤⼩
这⾥⽤到⼀个表, information_schema.tables；对应主要字段含义如下：
ABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使⽤的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据⼤⼩
INDEX_LENGTH：索引⼤⼩
按记录数据统计：
SELECT
table_schema,
table_name,
table_rows
FROM
TABLES
ORDER BY
table_rows DESC;
查询所有数据库占⽤磁盘空间⼤⼩的SQL语句
SELECT
TABLE_SCHEMA,
concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), ' MB' ) AS index_size
FROM
information_schema.TABLES
GROUP BY
TABLE_SCHEMA
ORDER BY
data_length DESC;
查询单个库中所有表磁盘占⽤⼤⼩的SQL语句
SELECT
TABLE_NAME,
concat( TRUNCATE ( data_length / 1024 / 1024, 2 ), ' MB' ) AS data_size,
concat( TRUNCATE ( index_length / 1024 / 1024, 2 ), ' MB' ) AS index_size
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'sor'
GROUP BY
TABLE_NAME
ORDER BY
data_length DESC;
查看⼀个库中的使⽤情况
SELECT
CONCAT( table_schema, '.', table_name ) AS 'Table Name',
CONCAT( ROUND( table_rows / 1000000, 4 ), 'M' ) AS 'Number of Rows',
CONCAT( ROUND( data_length /( 1024 * 1024 * 1024 ), 4 ), 'G' ) AS 'Data Size',
CONCAT( ROUND( index_length /( 1024 * 1024 * 1024 ), 4 ), 'G' ) AS 'Index Size',
CONCAT( ROUND(( data_length + index_length )/( 1024 * 1024 * 1024 ), 4 ), 'G' ) AS 'Total' FROM
information_schema.TABLES
WHERE
table_schema LIKE 'src';
⽣成测试数据
CREATE TABLE `vote_record` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR ( 20 ) NOT NULL,
`vote_id` INT ( 11 ) NOT NULL,
`group_id` INT ( 11 ) NOT NULL,
`create_time` date NOT NULL,
PRIMARY KEY ( `id` ),
KEY `index_user_id` ( `user_id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
CREATE TABLE `vote_record_memory` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR ( 20 ) NOT NULL,
`vote_id` INT ( 11 ) NOT NULL,
`group_id` INT ( 11 ) NOT NULL,
`create_time` date NOT NULL,
PRIMARY KEY ( `id` ),
KEY `index_user_id` ( `user_id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `rand_date`(sd DATE,ed DATE) RETURNS date DETERMINISTIC
BEGIN
DECLARE sub INT DEFAULT 0;
DECLARE ret DATE;
SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);
RETURN ret;
END $$
DELIMITER $$
CREATE DEFINER = `root` @`%` FUNCTION `rand_string` ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 DETERMINISTIC BEGIN DECLARE
chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str VARCHAR ( 255 ) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat( return_str, substring( chars_str, FLOOR( 1 + RAND( ) * 62 ), 1 ) );
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
DELIMITER $$
CREATE DEFINER = `root` @`%` PROCEDURE `add_vote_memory` ( IN n INT ) BEGIN
DECLARE
i INT DEFAULT 1;
WHILE ( i <= n ) DO
INSERT INTO vote_record_memory ( user_id, vote_id, group_id, create_time )
VALUES
(
rand_string ( 20 ), FLOOR( RAND( ) * 1000 ), FLOOR( RAND( ) * 100 ), rand_date('1980-1-1','2000-01-01')
);
SET i = i + 1;
END WHILE;
END $$
CALL add_vote_memory (1000);
SHOW PROCEDURE STATUS LIKE 'add_vote_memory';
SHOW CREATE PROCEDURE add_vote_memory;
DROP PROCEDURE add_vote_memory;
SELECT * FROM information_schema.routines WHERE routine_name='add_vote_memory';
参考。