Mysql 基本sql语句

---

2014-01-13 by dongnan

库相关

创建数据库：

# set 是设置字符集
create database redmine character set utf8;

列出全部数据库：

show databases;

删除数据库：

drop database test;

表相关

创建表：

# test
create table test (c1 int(10),c2 int(10), c3 int(10),c4 char(10));

# 大写小写等效
CREATE TABLE `grid` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `number` varchar(2) DEFAULT NULL COMMENT '策略编号',
  `name` varchar(32) DEFAULT NULL COMMENT '策略名称',
  `description` varchar(128) DEFAULT NULL COMMENT '策略描述',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看表结构：

desc test;

# 两个语句等效
show create table test;

删除表：

drop table test;

列出全部表(当前库)：

show tables;

复制表(包括结构、索引、数据)：

create table test1 like test;
insert test1 select * from test;

insert into

功能： insert into 语句用于向表格中插入新的行。

语法： insert into 表名称 values (值1, 值2,....)。

• 也可以指定列： insert into table_name (列1, 列2,...) values (值1, 值2,....)。

示例：

insert into post_rule (name,func,create_time,domain) values('帖子','post_add_post','2147483647',1);

delete

功能: 删除记录。

格式: delete from tbl_name [where 要删除的记录];

条件: where 子句指定哪些记录应该删除。它是可选的但是如果不选的话将会删除所有的记录。

不带where条件的语句清空整个表，千万注意：

delete from test;

为了删除特定的记录，可用where子句来选择所要删除的记录：

delete from test where c1="1";
delete from mysql.user where host="::1";

# 不等于
delete from test where user_id <> 14;

# 逻辑与
delete from test where id > 14 and id < 43;
# 逻辑或
delete from test where id=14 or id=43;

update

功能: 修改记录。

格式: update tbl_name set 要更改的列;

条件: where 要更新的记录,这里的 where 子句是可选的，因此如果不指定的话表中的每个记录都被更新。

为了更新特定的记录，使用where子句来选择记录：

update fund set track_objective='中证传媒指数' where id=4;

# 把整个表中的 字段1 赋值给 字段2（where条件可选）可以这么写：
update set accumulate = networth where fund_id != 6;

# 将index_id 为 null 设置index_id 为 4. 
update index_data set indexes_id=4 where indexes_id is null;

select

功能：查询数据。

查询排序：

# 默认 升序（asc）
mysql> select * from index_data where indexes_id = 1 limit 1;
+------+------------+-------------+----------+--------------+---------------------+------------+
| id   | trade_date | close_value | turnover | trade_amount | create_time         | indexes_id |
+------+------------+-------------+----------+--------------+---------------------+------------+
| 4043 | 2004-12-31 | 1000.0      | None     | None         | 2020-08-19 03:31:29 |          1 |
+------+------------+-------------+----------+--------------+---------------------+------------+
1 row in set (0.01 sec)

# 倒序 （desc）
mysql> select * from index_data where indexes_id = 1 order by id desc limit 1;
+-------+------------+-------------+-----------+-------------------+---------------------+------------+
| id    | trade_date | close_value | turnover  | trade_amount      | create_time         | indexes_id |
+-------+------------+-------------+-----------+-------------------+---------------------+------------+
| 12126 | 2020-08-18 | 6941.008    | 175367331 | 1.91997407328e+11 | 2020-08-19 07:20:35 |          1 |
+-------+------------+-------------+-----------+-------------------+---------------------+------------+
1 row in set (0.00 sec)

查询条件为NULL：

select * from index_data where indexes_id is null;

多个查询条件：

select * from fund_data where fund_id = 2 and timestamp > '2021-01-01' and networth > '0.44';

查询时间范围：

select * from fund_data where fund_id=2 and timestamp >= '20160101' && timestamp <= '20161231';

where 子句

功能: 有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法：

# 以下是SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

运算符：

=     #等于
!=    #不等于
>     #大于
<     #小于
>=     #大于或等于
<=     #小于或等于
BETWEEN   #介于一个包含范围内
LIKE      #搜索匹配的模式

等于：

select * from stat where id = 6 \G;

范围：

select * from stat where id >10 and id <50;
# 等效
select * from stat where id between 10 and 50;

匹配：

select ssl_cipher from user where ssl_cipher like "%root%" \G;
*************************** 1. row ***************************
ssl_cipher: root:x:0:0:root:/root:/bin/bash

in 子句

删除 loan_id 为 '201705190002', '201705190003'：

delete from table_name where loan_id in ('201705190002', '201705190003');

in + 字符串

select user from mysql.user where user in ('root','open') \G;
*************************** 1. row ***************************
user: open
*************************** 2. row ***************************
user: root

注意：这里一定要将字符串用单引号'' 标注起来；

alter table

功能：更改表结构。

格式：

# 如需在表中添加列，请使用下列语法:
alter table table_name ADD column_name datatype

# 要删除表中的列，请使用下列语法：
alter table table_name DROP COLUMN column_name

# 要改变表中列的数据类型，请使用下列语法：
alter table table_name ALTER COLUMN column_name datatype

添加字段：

alter table test.test add UdpYes int(11), add UdpNo int(11), add Tcp int(11);

# 添加新列(passwd) 位置为user_pwd后
alter table `ywwd` add `passwd` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '测试' AFTER user_pwd;

MySQL增加列的时候可以指定此列的位置，给指定位置加列需要两个关键字：

• FIRST，表示增加此列为第一个列。
• AFTER，表示增加在某个列之后。

删除字段：

# 删除多个列使用","隔开
alter table test.test drop column numOfCTViaNC, drop column numOfCTViaPop;

更改表的字符集：

alter table redmine.projects convert to character set utf8 collate utf8_general_ci;

修改字段：

alter table fund MODIFY code varchar(8) DEFAULT NULL COMMENT '基金代码';

增加索引：

# 添加普通索引
alter table favorite add index index_uid(uid);

# 添加多列索引
alter table reply add index index_pid_uid_create_time(pid,uid,create_time);

# 添加主键索引（PRIMARY KEY）
alter table `table_name` add primary key (`column`);

rename table

功能：重命名表

功能：rename 语句用于修改表名。

语法：rename table 原表名 to 新表名;

注意：当执行 RENAME 时，不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。 如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。

示例：

# 当前的表名
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| x_user          |
+-----------------+
1 row in set (0.00 sec)

# 重命名表
mysql> rename table x_user to user;
Query OK, 0 rows affected (0.00 sec)

# 成功
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)

show 语句

查看变量

mysql> show variables like 'max_con%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 1000  |
+--------------------+-------+
2 rows in set (0.00 sec)

# 临时修改此参数的值
mysql> set GLOBAL max_connections=2000;
mysql> set GLOBAL max_connect_errors=1000;

查看表引结构

show create table test;

Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

授权语句

为数据库授权用户：

# 创建数据库
create database `redmine-db` character set utf8;

# redmine用户对`redmine-db`数据库拥有全部权限
grant all privileges on `redmine-db`.* TO 'redmine'@'%' identified by 'password' WITH GRANT OPTION;

# 刷新权限  
flush privileges;

删除用户：

drop user 'rep2@'10.0.100.%';
flush privileges;

收回权限：

# 查看用户权限
show grants for zx_root;

# 赋予select权限
grant select on ywwd.* to zx_root;

# 回收select权限
revoke select on ywwd.*  from  zx_root;

# 使用多个权限同时赋予和回收，权限之间使用逗号分隔
grant select，update，delete，insert ywwd.* to dongnan;

# 刷新权限
flush  privileges ;

只读权限用户

grant select on `ywwd-db`.* TO 'dongnan'@'%' identified by 'password' WITH GRANT OPTION;  
# 刷新
flush  privileges ;

set 语句

SET 语句可以设置各种不同的变量类型，这些变量可以影响着服务器或者客户端的参数：

• 系统变量在 SET 语法中可以以 var_name 的方式来引用。
• 变量名前面用 GLOBAL 或 @@global 来表示这是一个全局变量。
• 变量名前面用 SESSION , @@session 或 @@ 来表示这是一个会话变量。
• 如果变量名前面没有附加的修饰词的话，那么 SET 只修改会话变量。

设置最大连接数

set GLOBAL max_connections=2000;  
set GLOBAL max_connect_errors=1000;

设置sql模式

set GLOBAL sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

慢查询日志

# 关闭慢查询日志    
SET GLOBAL slow_query_log = 'OFF';

# 启用/设置慢查询日志
SET GLOBAL long_query_time = 5;

参考

• Mysql truncate 清空表数据
• mysql 用户管理和权限设置
• WHERE 子句
• SET 和 SHOW语法
• SQL INSERT INTO 语句