当前位置 : 首页 » 文章分类 :  开发  »  MySQL-使用笔记

MySQL-使用笔记

mysql日常使用笔记与备忘


数据类型

AUTO_INCREMENT 自增值设置与修改

创建表时设置自增主键:

CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL auto_increment,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY  (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

创建表格后添加:

alter table users AUTO_INCREMENT=10000;

而且该语句也适用于修改现有表的id上, 比如大批量删除数据后,想id从654321退回123456开始

alter table users AUTO_INCREMENT=123456;

TIMESTAMP的默认值和自动更新

1、 如果定义时 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句都有,列值为默认使用当前的时间戳,并且自动更新。

2、 如果不使用DEFAULT或ON UPDATE子句,那么它等同于DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

3、 如果只有DEFAULT CURRENT_TIMESTAMP子句,而没有ON UPDATE子句,列值默认为当前时间戳但不自动更新。

4、 如果没用DEFAULT子句,但有ON UPDATE CURRENT_TIMESTAMP子句,列默认为0并自动更新。

5、 如果有一个常量值DEFAULT,该列会有一个默认值,而且不会自动初始化为当前时间戳。如果该列还有一个ON UPDATE CURRENT_TIMESTAMP子句,这个时间戳会自动更新,否则该列有一个默认的常量但不会自动更新。

换句话说,你可以使用当前的时间戳去初始化值和自动更新,或者是其中之一,也可以都不是。(比如,你在定义的时候可以指定自动更新,但并不初始化。)下面的字段定义说明了这些情况:

自动初始化和更新:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

只自动初始化:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

只自动更新
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

只是给一个常量(注:0000-00-00 00:00:00)
ts TIMESTAMP DEFAULT 0

MySQL的timestamp类型自动更新问题
http://www.yayu.org/look.php?id=144

11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html


enum 枚举类型

枚举类型对应的数字从1开始

创建带有enum类型字段的表:

CREATE TABLE `enum_tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('pending','success','closed') COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

修改enum字段的枚举值:

ALTER TABLE inviter_relationship_transaction CHANGE mode mode ENUM(‘auto’, ‘manual’, ‘task’) default ‘auto’ null;

关于 MySQL enum 类型的一些测试
https://laravel-china.org/articles/6927/some-tests-on-the-mysql-enum-type



sql易错

OR条件没加括号直接和其他条件AND

比如想查询指定user_id的没有订单号的数据,如果写成下面这样就大错特错了:

SELECT * FROM order
WHERE user_id = 2812 AND
  order_no IS NULL OR order_no = '' ;

正确的写法是用括号把或条件括起来:

SELECT * FROM order
WHERE user_id = 2812 AND
  ( order_no IS NULL OR order_no = '' );

mysql系统变量

查看mysql版本号

未连接到MySQL服务器mysql -v

没有连接到MySQL服务器,就想查看MySQL的版本。打开cmd,切换至mysql的bin目录,运行下面的命令即可:

e:\mysql\bin>mysql -V
mysql  Ver 14.14 Distrib 5.6.32, for Win32 (AMD64)
(版本为 5.6.32)

或者:

e:\mysql\bin>mysql -v

这个命令可以查看到更为详细的信息,因为它会用账号 ODBC,连接上MySQL服务器,默认连接到localhost上的3306端口。

select version();

已连接到mysql服务器后

MariaDB [uds]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.34-MariaDB |
+-----------------+
1 row in set (0.023 sec)

status;\s

MariaDB [uds]> status;
--------------
mysql  Ver 15.1 Distrib 10.3.7-MariaDB, for osx10.13 (x86_64) using readline 5.1

Connection id:        1336804
Current database:    uds
Current user:        root@10.111.158.144
SSL:            Not in use
Current pager:        less
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.1.34-MariaDB MariaDB Server
Protocol version:    10
Connection:        t-awsbj-uds-01.clap5vvkrarj.rds.cn-north-1.amazonaws.com.cn via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            10 days 9 hours 7 min 44 sec

Threads: 780  Questions: 263459304  Slow queries: 8524  Opens: 312565  Flush tables: 9  Open tables: 200  Queries per second avg: 293.756
--------------

show processlist 显示当前正在执行的mysql连接

show processlist;
如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接。
show processlist;只列出前100条,如果想全列出请使用 show full processlist;


information_schema

在MySQL 5.0以后的版本中,INFORMATION_SCHEMA中的表存储了系统相关信息。

SCHEMATA表:提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表:提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema,表类型,表引擎,创建时间等信息。是show tables from schemaname的结果取之此表。

information_schema.processlist 查看各ip连接数

从 information_schema 库的 processlist 表中查

select SUBSTRING_INDEX(host,':',1) as ip , count(*)
from information_schema.processlist
group by ip;

通过直接执行mysql命令也可以实现:

mysql -u root -h127.0.0.1 -e"show processlist\G;"| egrep "Host\:" | awk -F: '{ print $2 }'| sort | uniq -c
mysql -u root -h127.0.0.1 --skip-column-names -e"show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq –c

information_schema.tables.create_time

在MySQL 5.0以后的版本中,也可以查询 INFORMATION_SCHEMA 库中的 TABLES 表中的 CREATE_TIME 字段来看表的创建时间,
即information_schema.tables.create_time

或者,可以使用SHOW TABLE STATUS命令显示表的相关信息。
例如,对于mysql数据库中的user表:
SHOW TABLE STATUS LIKE 'user'

注意:在innoDB引擎中,information_schema.tables.create_time 是表结构最后一次被 ALTER TABLE 语句更新的时间,比如用 ALTER TABLE 改变一个列的默认值时此字段会被更新。
但MyISAM引擎又不同,ALTER TABLE时不会更新此字段。
如果想看表的精确创建时间,应该看数据库文件系统中表的 .frm 文件的创建时间。但Linux系统中文件是不保存创建时间的,所以也没法看到。

When was my table last ALTERed?
https://stackoverflow.com/questions/9537248/when-was-my-table-last-altered


show status 查看所有status

show status; 查看所有变量

show status like '%变量名%'; 查看某个具体变量值

常用变量说明:
Aborted_clients 由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时,已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载)
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。

Max_used_connections 查看最大并发连接数

即mysql运行过程中曾经达到的最大连接数
show global status like 'Max_used_connections';

https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Max_used_connections

Threads_connected 查看当前实时连接数

show global status like 'Threads%';
Threads_connected 当前的实时连接数,
Threads_created 表示创建过的线程数
Threads_running 这个数值指的是激活的连接数,这个数值一般远低于connected数值

MariaDB [uds]> show global status like 'Threads%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Threads_cached    | 0        |
| Threads_connected | 1369     |
| Threads_created   | 15899277 |
| Threads_running   | 1        |
+-------------------+----------+

https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Threads_connected


show variables 查看所有variable

show variables; 查看所有变量

show variables like '%变量名%' 查看某个具体变量值

5.1.7 Server System Variables
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

max_connections 查看my.cnf中配置的最大连接数

show variables like 'max_connections';
可以在/etc/my.cnf里面设置数据库的最大连接数

[mysqld]
max_connections = 1000

默认值151,最小值1,最大值100000

mariadb - max_connections
https://mariadb.com/kb/en/library/server-system-variables/#max_connections

mysql - max_connections
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_connections


datadir 物理文件存放位置

show global variables like "%datadir%";

character_set_ 查看mysql字符集

SHOW VARIABLES LIKE 'character_set_%';
没设置的情况下,默认就是utf-8

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

bind_address 服务器绑定ip

查看mysql server绑定ip
show variables like 'bind_address';
例如:

mysql> show variables like 'bind_address';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+
1 row in set (0.21 sec)

bind-address是MYSQL用来监听某个单独的TCP/IP连接,只能绑定一个IP地址,被绑定的IP地址可以映射多个网络接口.
可以是IPv4,IPv6或是主机名,但需要在MYSQL启动的时候指定(主机名在服务启动的时候解析成IP地址进行绑定).
默认是*,表示接收所有的IPv4 或 IPv6 连接请求

在/etc/my.cnf中配置:

  • 接收所有的IPv4 或 IPv6 连接请求
    0.0.0.0 接受所有的IPv4地址
    :: 接受所有的IPv4 或 IPv6 地址
    IPv4-mapped 接受所有的IPv4地址或IPv4邦定格式的地址(例 ::ffff:127.0.0.1)
    IPv4(IPv6) 只接受对应的IPv4(IPv6)地址

工具/函数


mysqldump数据库(表)导入导出

远程数据库(表)导出到本地数据库(表)文件

导出数据库/表

(1)导出数据库

# 将192.168.1.1主机上的mydb数据库导出到本地的mydb.bak文件中
mysqldump -h192.168.1.1 -uroot -p123456 --databases mydb > mydb.bak;

# 将本地mysql服务器上的mydb1数据库导出到本地的mydb1.bak文件中
mysqldump  -uroot -p123456 --databases mydb1 > mydb1.bak;

(2)导出数据表

# 将192.168.1.1主机上的mydb数据库的tb1数据表导出到本地的tb1.bak文件中
mysqldump -h192.168.1.1 -uroot -p123456 mydb tb1 > tb1.bak;

# 将本地主机上的mydb1数据库的tb2数据表导出到本地的tb2.bak文件中
mysqldump  -uroot -p123456 mydb1 tb2 > tb2.bak;

导入数据库/表

(1)导入数据库
在本地数据库中创建相对应导出的数据库mydb同名的数据库:
mysql> create database mydb;

然后退出数据库,再使用以下的 命令导入数据库文件mydb.bak到本地数据库mydb中:
mysql -uroot -p123456 mydb < /root/data/mydb.bak;

或者进入mysql中,使用sourc指令完成数据库导入,如下:
mysql> source /root/data/mydb.bak;
/root/data/mydb.bak是远程数据库mydb导出文件的本地存放位置

(2)导入数据表
在本地数据库中创建相对应导出的数据库mydb同名的数据库:
mysql> create database mydb;

然后在mysql中使用source指令来完成数据表的导入,如下:
mysql> source /root/data/tb1.bak;
/root/data/tb1.bak是远程数据表tb1导出文件的本地存放位置

MySQL数据库(表)的导入导出(备份和还原)
https://blog.csdn.net/Deutschester/article/details/6866842

windows上导出文件无法导入

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:
shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.4, “Connection Character Sets and Collations”), so the dump file will not load correctly. To work around this issue, use the –result-file option, which creates the output in ASCII format:
shell> mysqldump [options] --result-file=dump.sql

4.5.4 mysqldump — A Database Backup Program
https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

mysqldump命令详解

有3种方式来调用mysqldump:
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] —database DB1 [DB2 DB3…]
shell> mysqldump [options] –all–database
如果没有指定任何表或使用了—database或–all–database选项,则转储整个数据库。

–host=host_name,-h host_name
从给定主机的MySQL服务器转储数据。默认主机是localhost。

–user=user_name,-u user_name
连接服务器时使用的MySQL用户名。

–password[=password],-p[password]
连接服务器时使用的密码。如果你使用短选项形式(-p),不能在选项和密码之间有一个空格。如果在命令行中,忽略了–password或-p选项后面的 密码值,将提示你输入一个。

–opt
该选项是速记;等同于指定 –add-drop-tables–add-locking –create-option –disable-keys–extended-insert –lock-tables –quick –set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启,但可以用–skip-opt禁用。要想只禁用确信用-opt启用的选项,使用–skip形式;例如,–skip-add-drop-tables或–skip-quick。

–compress,-C
压缩在客户端和服务器之间发送的所有信息(如果二者均支持压缩)。

–lock-tables,-l
开始转储前锁定所有表。

mysqldump命令详解
http://blog.51cto.com/wangwei007/980586

MySQL mysqldump命令
https://www.jianshu.com/p/14947ba6862b


mysql单服务器内表间数据复制

同一数据库内insert select

1、表结构相同的表,且在同一数据库(如,table1,table2)

# 完全复制
insert into table1 select * from table2;
# 不复制重复纪录
insert into table1 select distinct * from table2;
# 前五条纪录
insert into table1 select top 5 * from table2;

不同数据库间insert select

2、不在同一数据库中(如,db1 table1,db2 table2)

# 完全复制
insert into db1.table1 select * from db2.table2;
# 不复制重复纪录
insert into db1.table1 select distinct * from db2table2;
# 前五条纪录
insert into tdb1.able1 select top 5 * from db2table2;

表结构不相同insert select

如果表tb1和tb2只有部分字段是相同的,要实现将tb1中的部分字段导入到tb2中相对应的相同字段中,则使用以下命令:

insert into db2.tb2(字段1,字段2,字段3……)
select  字段1‘,字段2’,字段3‘,……  from db1.tb1;

Mysql-两个表之间复制数据
https://blog.csdn.net/qingwuh/article/details/81350470


拷贝创建新表

创建新表同时拷贝数据(丢失约束等)

复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用alter table语句添加,而且容易搞错。

只拷贝表结构(丢失约束等)

只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
其中的where只要是个恒为false的表达式即可,此方法同样会丢失约束等额外信息

只拷贝表结构(保留约束等)

CREATE TABLE 新表 LIKE 旧表
此方法会保留表的约束等所有额外信息,推荐使用

创建新表并复制数据(保留约束等)

完整复制表的方法:
先创建新表,保留约束等,在插入旧表的所有数据
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

MySQL复制表结构表数据
https://www.cnblogs.com/emanlee/p/5140670.html


清空表truncate和delete

truncate table wp_comments;
delete from wp_comments;
其中truncate操作中的table可以省略。这两者都是将wp_comments表中数据清空,不过也是有区别的,如下:

truncate是整体删除(速度较快), delete是逐条删除(速度较慢)。
truncate不写服务器log,delete写服务器log,也就是truncate效率比delete高的原因。
truncate不激活trigger(触发器),但是会重置Identity(标识列、自增字段),相当于自增列会被置为初始值,又重新从1开始记录,而不是接着原来的ID数。而delete删除以后,Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
如果只需删除表中的部分记录,只能使用DELETE语句配合where条件。 DELETE FROM wp_comments WHERE……


now()和sysdate()区别

mysql的官方解释:
NOW() returns a constant time that indicates the time at which the statement began to execute.
This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

now()函数返回语句开始执行的时间;而sysdate()返回函数执行时的时间。

mysql now() sysdate() 区别
https://my.oschina.net/friendship/blog/510532


LAST_INSERT_ID()

简单说来,就是这个函数将返回插入的那条记录在表中自增的那个字段的值,一般我们都给那个自增字段命名为ID。这样就可以返回刚插入的记录的ID值了。

这个函数是基于connection的,也就是不会被其他客户端的connection影响到,所以结果是准确的。如果使用select max(id) from table,在高密度的插入请求下,是有可能出问题的,返回错误值

Mysql中LAST_INSERT_ID()的函数使用详解
https://blog.csdn.net/hsd2012/article/details/51464409


基础sql

重复数据取最大/最小

ID相同取最小TYPE

-- 方法1:(推荐在数据量较大时使用)
select * from Temp A where TYPE=(select min(TYPE) from Temp where ID=A.ID)

-- 方法2:
Select * from Temp A where not exists(select 1 from Temp where ID=A.ID and TYPE<A.TYPE)

-- 方法3:
select A.* from Temp A join (select min(TYPE)TYPE,ID from Temp group by ID) B on A.ID=B.ID and A.TYPE=B.TYPE

Sql 处理重复记录(相同ID取另一字段最大值)
https://www.norbread.com/2018/01/14/sql-duplicated/


update

ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE 为Mysql特有语法,作用是当insert已经存在的记录时,执行Update

如果在INSERT语句末尾指定了 ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。
例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果:

INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

update多列

update user u
set u.name = '张三', u.mobile = '13613661366'
where u.id = 23;

update条件列

FROM manager AS m2
改为
FROM (select * from manager) AS m2

Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source


update select根据查询更新

当你希望更新一批值,且值是通过select条件查询出来时
解决思路 1:
使用 INNER JOIN (最简洁)

UPDATE A a INNER JOIN B b ON b.key_id = a.key_id
SET a.val = 2 WHERE b.satisfy = 1

解决思路 2:

UPDATE A a, (SELECT A.id from A
             LEFT JOIN B ON B.key_id= A.key_id
             WHERE B.satisfy = 1) b
SET a.val = 2
WHERE a.id = b.id

update返回值与useAffectedRows

比如如下mybatis sql语句:

@Update({"UPDATE user SET name = null WHERE id = #{id}"})
void updateUserProfileById(@Param("id") long id);

默认情况下,mybatis 的 update 操作的返回值是 matched 的记录数,并不是受影响的记录数。
严格意义上来将,这并不是 mybatis 的返回值,mybatis 仅仅只是返回的数据库连接驱动(通常是 JDBC )的返回值

通过对 JDBC URL 显式的指定 useAffectedRows选项,我们将可以得到受影响的记录的条数:
jdbc:mysql://${jdbc.host}/${jdbc.db}?useAffectedRows=true

那么有没有办法让 mybatis 的 update 操作的返回值是受影响的行数呢。因为我们业务逻辑中有时会根据这个返回值做业务判断。答案当然是有的。
修改数据库链接配置为:增加了 useAffectedRows 字段信息。

JDBC默认返回的是符合的行数Rows matched, 如果想返回修改过( Changed)的行数 ,需要使用useAffectedRows参数
useAffectedRows的含义 :是否用受影响的行数替代查找到的行数来返回数据

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?useAffectedRows=true
jdbc.username=root
jdbc.password=123456

mybatis 中的 update 返回值你真的明白吗
https://www.jianshu.com/p/80270b93082a

聊聊Mybatis Update操作返回值
https://notes.wanghao.work/2017-09-06-%E8%81%8A%E8%81%8AMybatis-Update%E6%93%8D%E4%BD%9C%E8%BF%94%E5%9B%9E%E5%80%BC.html


不等号

mysql中用 <>!= 都是可以的,但sqlserver中不识别 !=,所以建议用 <>

索引

查看索引

show index from table;

创建索引

create index vehicle_info_ibfk_1 on vehicle_info (car_order_id);

key和index区别

KEY
key 是数据库的物理结构,它包含两层意义,一是约束(偏重于约束和规范数据库的结构完整性),二是索引(辅助查询用的)。包括primary key, unique key, foreign key 等。

  • primary key 有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个index;
  • unique key 也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个index;
  • foreign key也有两个作用,一是约束作用(constraint),规范数据的引用完整性,但同时也在这个key上建立了一个index;
    MySQL中的key是同时具有constraint和index的意义。

另外,在MySQL中,对于一个Primary Key的列,MySQL已经自动对其建立了Unique Index,无需重复再在上面建立索引了。

INDEX
index是数据库的物理结构,它只是辅助查询的,它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。索引要分类的话,分为前缀索引、全文本索引等;因此,索引只是索引,它不会去约束索引的字段的行为。


查看创建sql

查看创建表的sql

show create table table_name;

查看创建数据库的sql

show create database database_name;

查看表有哪些列

desc table_name;

增加删除列

删除列

ALTER TABLE table_name DROP COLUMN field_name;

或者不加column关键字也行

ALTER TABLE table_name DROP field_name;

注意要删除的列不能是约束的一部分,比如group_type是多列唯一约束的其中一列,则删除时提示找不到列:
ERROR 1072 (42000): Key column ‘group_type’ doesn’t exist in table
需要先将唯一约束删除才能继续。

添加列

alter table 表名 add column 列名 varchar(30);
alter table 表名 add column `data_type` TINYINT NOT NULL DEFAULT 1 COMMENT '数据类型 0:unknown 1:male 2:owner';

添加列时指定字段顺序

FIRST,添加为第一列
AFTER col_name,添加到指定列名后
默认是添加为最后一列

ALTER TABLE user ADD COLUMN name varchar(128) null COMMENT '姓名' FIRST;
ALTER TABLE user ADD COLUMN name varchar(128) null COMMENT '姓名' AFTER id;

https://dev.mysql.com/doc/refman/5.6/en/alter-table.html#alter-table-add-drop-column

修改列名

修改列名

alter table table_name CHANGE COLUMN id user_id BIGINT NOT NULL COMMENT '用户id'

调整列顺序

alter table inviter_relationship_yym modify inviter_role tinyint(4) NOT NULL DEFAULT '0' after car_model;

修改列属性

alter table table_name modify column_name varchar(22);

外键约束

可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
FOREIGN KEY 约束可定义在列级,也可以定义在表级。
CONSTRAINT关键字可为约束命名。
ON DELETE CASCADE 声明级联删除
ON UPDATE CASCADE 声明级联更新

定义表时添加外键约束

CREATE TABLE "ais"."re_ais_opencloseclass_agent" (
"agent_id" int4 NOT NULL,
"airline" varchar(2) COLLATE "default" NOT NULL,
"savetime" int8 NOT NULL,
"saveuser" text COLLATE "default" NOT NULL,
CONSTRAINT "idx_re_ais_opencloseclass_agent_pk" PRIMARY KEY ("agent_id"),
CONSTRAINT "re_ais_opencloseclass_agent_agent_id_fkey1" FOREIGN KEY ("agent_id") REFERENCES "ais"."re_ais_base_agent" ("agent_id") ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT "re_ais_opencloseclass_agent_agent_id_airline_key" UNIQUE ("agent_id", "airline")
)

在现有表上添加外键约束

ALTER TABLE Orders
ADD
CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

修改约束

若要修改主键约束、外键约束、唯一约束、检查约束,必须先删除现有的约束,然后再用新定义重新创建该约束。

删除外键约束

ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name[1,...n]

唯一约束

唯一键约束,可以在一个列上添加约束,也可以在多个列上添加唯一约束。

在一列上添加唯一约束,主要是让该列在表中只能有唯一的一行,例如注册邮箱时的邮箱名、手机号等信息

创建表时添加唯一约束

在username列上添加唯一约束

CREATE TABLE `t_user` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(18) NOT NULL unique,
    `password` varchar(18) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=gbk;

在多列上添加唯一约束

CREATE TABLE `jw_resource` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `resource_name` VARCHAR(128) DEFAULT NULL,
  `resource_type` TINYINT(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `resource_name` (`resource_name`,`resource_type`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

在已有表上添加唯一约束

ALTER TABLE `t_user` ADD unique(`username`);

或者:

create unique index UserNameIndex on 't_user' ('username');

在多列上添加唯一约束:

ALTER TABLE table_name
ADD UNIQUE KEY index_name(resource_name, resource_type);

查看唯一约束

show index from table_name;

修改唯一约束

必须先删除唯一约束,再添加唯一约束

删除唯一约束

注意唯一约束用index关键字,因为唯一约束其实就是索引

ALTER TABLE table_name DROP INDEX index_name;

MySQL中添加唯一约束和联合唯一约束
https://blog.csdn.net/yumushui/article/details/38960619


删除重复行

删除所有重复行

用in

delete from table1
where user_id in (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);

或者用exists

delete from table1
where exists (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);

mariadb中都会报错:
[HY000][1093] Table ‘table1’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data
因为同时进行查询和修改。

解决方法:查询语句外面再套一个查询形成一张临时表

delete from drive_booking
where user_id in (
    select *
    from (
      SELECT user_id
      FROM drive_booking
      GROUP BY user_id
      HAVING count(*) > 1) temp_table
);

重复行只保留一条

删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录

delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

上面这条语句在mysql中执行会报错,因为同时对表进行查询和更新,mysql不支持这种方式。oracel和msserver都支持这种方式。

解决方法:查询语句外面再套一个查询形成一张临时表

delete from php_user where
username in (select username from ( select username from php_user group by username having count(username)>1) a)
and id not in ( select min(id) from (select min(id) as id from php_user group by username having count(username)>1 ) b)

上一篇 Java-面试-集合框架

下一篇 LeetCode.033.Search in Rotated Sorted Array

阅读
6,689
阅读预计29分钟
创建日期 2018-03-08
修改日期 2018-11-16
类别
目录
  1. 数据类型
    1. AUTO_INCREMENT 自增值设置与修改
    2. TIMESTAMP的默认值和自动更新
    3. enum 枚举类型
      1. 创建带有enum类型字段的表:
      2. 修改enum字段的枚举值:
    4. sql易错
      1. OR条件没加括号直接和其他条件AND
  2. mysql系统变量
    1. 查看mysql版本号
      1. 未连接到MySQL服务器mysql -v
      2. select version();
      3. status; 或 \s
    2. show processlist 显示当前正在执行的mysql连接
    3. information_schema
      1. information_schema.processlist 查看各ip连接数
      2. information_schema.tables.create_time
    4. show status 查看所有status
      1. Max_used_connections 查看最大并发连接数
      2. Threads_connected 查看当前实时连接数
    5. show variables 查看所有variable
      1. max_connections 查看my.cnf中配置的最大连接数
      2. datadir 物理文件存放位置
      3. character_set_ 查看mysql字符集
      4. bind_address 服务器绑定ip
  3. 工具/函数
    1. mysqldump数据库(表)导入导出
      1. 导出数据库/表
      2. 导入数据库/表
      3. windows上导出文件无法导入
      4. mysqldump命令详解
    2. mysql单服务器内表间数据复制
      1. 同一数据库内insert select
      2. 不同数据库间insert select
      3. 表结构不相同insert select
    3. 拷贝创建新表
      1. 创建新表同时拷贝数据(丢失约束等)
      2. 只拷贝表结构(丢失约束等)
      3. 只拷贝表结构(保留约束等)
      4. 创建新表并复制数据(保留约束等)
    4. 清空表truncate和delete
    5. now()和sysdate()区别
    6. LAST_INSERT_ID()
  4. 基础sql
    1. 重复数据取最大/最小
    2. update
      1. ON DUPLICATE KEY UPDATE
      2. update多列
      3. update条件列
      4. update select根据查询更新
      5. update返回值与useAffectedRows
    3. 不等号
    4. 索引
      1. 查看索引
      2. 创建索引
      3. key和index区别
    5. 查看创建sql
      1. 查看创建表的sql
      2. 查看创建数据库的sql
      3. 查看表有哪些列
    6. 增加删除列
      1. 删除列
      2. 添加列
      3. 添加列时指定字段顺序
      4. 修改列名
      5. 调整列顺序
      6. 修改列属性
    7. 外键约束
      1. 定义表时添加外键约束
      2. 在现有表上添加外键约束
      3. 修改约束
      4. 删除外键约束
    8. 唯一约束
      1. 创建表时添加唯一约束
      2. 在已有表上添加唯一约束
      3. 查看唯一约束
      4. 修改唯一约束
      5. 删除唯一约束
    9. 删除重复行
      1. 删除所有重复行
      2. 重复行只保留一条
百度推荐