mysql的管理

  1. #Window 启动mysql
  2. cd c:/mysql/bin
  3. mysqld --console
  4. #window 关闭mysql
  5. cd c:/mysql/bin
  6. mysqladmin -uroot shutdown
  8. #Linux
  9. ps -ef | grep mysqld #查看是否正在运行
  10. #Linux启动 mysql
  11. root@host# cd /usr/bin
  12. ./mysqld_safe &
  13. #Linux 关闭mysql
  14. root@host# cd /usr/bin
  15. ./mysqladmin -u root -p shutdown
  16. Enter password: ******
  1. #用户设置
  2. root@host# mysql -u root -p
  3. Enter password:*******
  4. mysql> use mysql;
  5. Database changed
  7. mysql> INSERT INTO user 
  8.           (host, user, password, 
  9.            select_priv, insert_priv, update_priv) 
  10.            VALUES ('localhost', 'guest', 
  11.            PASSWORD('guest123'), 'Y', 'Y', 'Y');
  12. Query OK, 1 row affected (0.20 sec)
  14. mysql> FLUSH PRIVILEGES;
  15. Query OK, 1 row affected (0.01 sec)
  17. mysql> SELECT host, user, password FROM user WHERE user = 'guest';
  18. +-----------+---------+------------------+
  19. | host      | user    | password         |
  20. +-----------+---------+------------------+
  21. | localhost | guest | 6f8c114b58f2ce9e |
  22. +-----------+---------+------------------+
  23. 1 row in set (0.00 sec)
  25. 在添加用户时，请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。 
  26. 你可以在以上实例看到用户密码加密后为： 6f8c114b58f2ce9e.
  28. 注意：在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
  30. 注意：password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
  32. 注意：在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。
  34. 如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。
  36. 你可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 'Y' 即可，用户权限列表如下：
  38. Select_priv
  39. Insert_priv
  40. Update_priv
  41. Delete_priv
  42. Create_priv
  43. Drop_priv
  44. Reload_priv
  45. Shutdown_priv
  46. Process_priv
  47. File_priv
  48. Grant_priv
  49. References_priv
  50. Index_priv
  51. Alter_priv
  52. 另外一种添加用户的方法为通过SQL的 GRANT 命令，以下命令会给指定数据库TUTORIALS添加用户 zara ，
  53. 密码为 zara123 。
  55. root@host# mysql -u root -p
  56. Enter password:*******
  57. mysql> use mysql;
  58. Database changed
  60. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  61.     -> ON TUTORIALS.*
  62.     -> TO 'zara'@'localhost'
  63.     -> IDENTIFIED BY 'zara123';
  64. 以上命令会在mysql数据库中的user表创建一条用户信息记录。
  66. 注意: MySQL 的SQL语句以分号 (;) 作为结束标识。
  1. /etc/my.cnf 文件配置
  2. 一般情况下，你不需要修改该配置文件，该文件默认配置如下：
  4. [mysqld]
  5. datadir=/var/lib/mysql
  6. socket=/var/lib/mysql/mysql.sock
  8. [mysql.server]
  9. user=mysql
  10. basedir=/var/lib
  12. [safe_mysqld]
  13. err-log=/var/log/mysqld.log
  14. pid-file=/var/run/mysqld/mysqld.pid
  15. 在配置文件中，你可以指定不同的错误日志文件存放的目录，一般你不需要改动这些配置。

mysql -h 127.0.0.1 -P 3306 -u 账号 -p密码

show databases;

use 数据库名;

选择数据库

创建用户

mysql 8.x
create user ‘test1’@’localhost’ identified by ‘‘密码’;
flush privileges; # 刷新权限
grant all privileges on . to ‘test1’@’localhost’ with grant option; # 授权
flush privileges;

  1. mysql -h 127.0.0.1 -P 3306 -u 账号 -p密码
  2. show databases; // 显示所有的数据库
  3. use lijunyang; // 进入数据库

show tables;

show tables like ‘%xxx%’

desc table_name;

查看表结构

show create table table_name;

查看数据库表创建的sql语句

show columns from 数据表;

show index from 数据表;

show tab status like [from db_name] [like ‘pattern’] \G;

  1. mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息
  2. mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
  3. mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

创建数据库

create DATABASE 数据库名;

  1. mysql -h 127.0.0.1 -P 3306 -u 账号 -p密码
  2. create DATABASE RUNOOB;

删除数据库

drop database 数据库名;

  1. mysql -h 127.0.0.1 -P 3306 -u 账号 -p密码
  2. drop DATABASE RUNOOB;

创建表

image.png

  1. 创建表
  2. 语法:CREATE TABLE table_name (column_name column_type);
  3. IF NOT EXISTS 判断表是否存在
  4. AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  5. PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
  6. ENGINE 设置存储引擎，CHARSET 设置编码。
  7. INT UNSIGNED 无符号int0
  9. CREATE TABLE IF NOT EXISTS `user` (
  10. `userId` int(18) UNSIGNED NOT NULL AUTO_INCREMENT,
  11. `userName` varchar(255) NOT NULL,
  12. `password` varchar(255) NOT NULL,
  13. `Telephone` varchar(255) DEFAULT NULL,
  14. `email` varchar(255) DEFAULT '',
  15. `createTime` datetime NOT NULL,
  16. `updatePasswordTime` datetime NOT NULL,
  17. PRIMARY KEY (`userId`)
  18. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  20. CREATE TABLE `historyPass` (
  21. `userId` int(18) NOT NULL,
  22. `updatePasswordTime` datetime NOT NULL,
  23. `password` varchar(255) NOT NULL,
  24. PRIMARY KEY (`userId`),
  25. CONSTRAINT `FK_ID` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
  26. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除表

drop table table_name; 删除表

MySQL常用命令

查询自增值，photos为数据库名，tableName为表名
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema=’photos’ and table_name=”tableName”;

修改自增键初始值
alter table table_name auto_increment=n;
注意n只能大于已有的auto_increment的整数值,小于的值无效.

新增数据
INSERT INTO user (userName,password,Telephone,email,createTime,updatePasswordTime) VALUES (‘账号’,’密码’,’1891605’,’@163.com’,’2018-06-24 22:28:00’,’2018-06-24 22:28:00’)

select * from goods_list;

select * from goods_list limit 0,10; // 分页查询，第1页，10条

select * from goods_list g, fileSavePath f where g.pathId = f.fileId; // 多表联合查询

select from runoob_tbl where runoob_author like ‘%COM’; // LIKE与%号配合使用，查找COM的内容的数据

select * from tablename order by field1, [field2…] [ASC | [DESC]]; // order by 字段名 升序 | 降序_

UNION 连接 两个select

_MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会默认删除重复的数据。

  1. SELECT expression1, expression2, ... expression_n
  2. FROM tables
  3. [WHERE conditions]
  4. UNION [ALL | DISTINCT] // ALL 会删除重复，DISTINCT 不会删除重复
  5. SELECT expression1, expression2, ... expression_n
  6. FROM tables
  7. [WHERE conditions];

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

  1. SELECT column_name, function(column_name)
  2. FROM table_name
  3. WHERE column_name operator value
  4. GROUP BY column_name;
  7. mysql> set names utf8;
  8. mysql> SELECT * FROM employee_tbl;
  9. +----+--------+---------------------+--------+
  10. | id | name   | date                | singin |
  11. +----+--------+---------------------+--------+
  12. |  1 | 小明 | 2016-04-22 15:25:33 |      1 |
  13. |  2 | 小王 | 2016-04-20 15:25:47 |      3 |
  14. |  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
  15. |  4 | 小王 | 2016-04-07 15:26:14 |      4 |
  16. |  5 | 小明 | 2016-04-11 15:26:40 |      4 |
  17. |  6 | 小明 | 2016-04-04 15:26:54 |      2 |
  18. +----+--------+---------------------+--------+
  19. 6 rows in set (0.00 sec)
  21. mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
  22. +--------+----------+
  23. | name   | COUNT(*) |
  24. +--------+----------+
  25. | 小丽 |        1 |
  26. | 小明 |        3 |
  27. | 小王 |        2 |
  28. +--------+----------+
  29. 3 rows in set (0.01 sec)
  31. 使用 WITH ROLLUP
  32. WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
  34. 例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：
  36. mysql> SELECT name, SUM(singin) as singin_count FROM
  37. employee_tbl GROUP BY name WITH ROLLUP;
  38. +--------+--------------+
  39. | name   | singin_count |
  40. +--------+--------------+
  41. | 小丽 |            2 |
  42. | 小明 |            7 |
  43. | 小王 |            7 |
  44. | NULL   |           16 |
  45. +--------+--------------+
  46. 4 rows in set (0.00 sec)
  47. 其中记录 NULL 表示所有人的登录次数。
  49. 我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
  51. select coalesce(a,b,c);
  52. 参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null
  53. ，则返回为null（没意义）。
  55. 以下实例中如果名字为空我们使用总数代替：
  57. mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl 
  58. GROUP BY name WITH ROLLUP;
  59. +--------------------------+--------------+
  60. | coalesce(name, '总数') | singin_count |
  61. +--------------------------+--------------+
  62. | 小丽                   |            2 |
  63. | 小明                   |            7 |
  64. | 小王                   |            7 |
  65. | 总数                   |           16 |
  66. +--------------------------+--------------+
  67. 4 rows in set (0.01 sec)

DELETE FROM table_name [WHERE Clause]
UPDATE tablename SET variablename = value [where variablename = value]

  1. select * from goods_list g limit 0,1 #分页返回
  2. select * from goods_list order by goodsId DESC limit 0,3; #升序
  3. select * from goods_list  order by goodsId ASC limit 0,3; #降序
  1. ## 修改表结构
  2. ## ALTER TABLE 表名 MODIFY 列名 数据类型
  3. ALTER TABLE user MODIFY userId int(18) UNSIGNED NOT NULL;
  5. ## ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型 ## 修改表结构，可以重命名字段名
  6. ALTER TABLE user CHANGE userId UserId int(18) UNSIGNED NOT NULL;
  8. ## ALTER TABLE 表名 DROP 列名 ## 删除一个字段
  9. ## ALTER TABLE 表名 ADD 列名 数据类型 ## 新增一个字段
  11. ## 设置字段默认值，方法一，就不会有NULL了
  12. ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
  13. ## 设置字段默认值，方法二
  14. mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
  15. mysql> SHOW COLUMNS FROM testalter_tbl;
  16. +-------+---------+------+-----+---------+-------+
  17. | Field | Type    | Null | Key | Default | Extra |
  18. +-------+---------+------+-----+---------+-------+
  19. | c     | char(1) | YES  |     | NULL    |       |
  20. | i     | int(11) | YES  |     | 1000    |       |
  21. +-------+---------+------+-----+---------+-------+
  22. 2 rows in set (0.00 sec)
  23. ## 删除掉default
  24. mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
  25. mysql> SHOW COLUMNS FROM testalter_tbl;
  26. +-------+---------+------+-----+---------+-------+
  27. | Field | Type    | Null | Key | Default | Extra |
  28. +-------+---------+------+-----+---------+-------+
  29. | c     | char(1) | YES  |     | NULL    |       |
  30. | i     | int(11) | YES  |     | NULL    |       |
  31. +-------+---------+------+-----+---------+-------+
  32. 2 rows in set (0.00 sec)
  34. ### 创建表
  35. CREATE TABLE `historyPass` (
  36.   `userId` int(18) UNSIGNED NOT NULL,
  37.   `updatePasswordTime` datetime NOT NULL,
  38.   `password` varchar(255) NOT NULL,
  39.   PRIMARY KEY (`userId`),
  40.   CONSTRAINT `FK_ID` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
  41. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  44. ## 查询用此表字段做外键的表信息
  45. select
  46. TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
  47. from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  48. where CONSTRAINT_SCHEMA ='lijunyang' AND
  49. REFERENCED_TABLE_NAME = 'user';
  51. ## 添加外键
  52. SET FOREIGN_KEY_CHECKS=0;
  53. alter table class_list add constraint `FK_TEACHERID` foreign key(`t_id`) 
  54. references teacher_list(`t_id`);
  55. SET FOREIGN_KEY_CHECKS=1;
  57. ## 删除外键 FK_ID是外键名，tableName是表名
  58. alter table tableName drop foreign key FK_ID;
  59. ## 修改表类型
  60. mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
  61. mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
  62. *************************** 1. row ****************
  63.            Name: testalter_tbl
  64.            Type: MyISAM
  65.      Row_format: Fixed
  66.            Rows: 0
  67.  Avg_row_length: 0
  68.     Data_length: 0
  69. Max_data_length: 25769803775
  70.    Index_length: 1024
  71.       Data_free: 0
  72.  Auto_increment: NULL
  73.     Create_time: 2007-06-03 08:04:36
  74.     Update_time: 2007-06-03 08:04:36
  75.      Check_time: NULL
  76.  Create_options:
  77.         Comment:
  78. 1 row in set (0.00 sec)
  80. ###修改表名
  81. mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
  83. ## 索引

Mysql 连接查询

  • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。 ``` mysql> use RUNOOB; Database changed mysql> SELECT * FROM tcount_tbl; +———————-+———————+ | runoob_author | runoob_count | +———————-+———————+ | 菜鸟教程 | 10 | | RUNOOB.COM | 20 | | Google | 22 | +———————-+———————+ 3 rows in set (0.01 sec)

mysql> SELECT * from runoob_tbl; +—————-+———————-+———————-+————————-+ | runoob_id | runoob_title | runoob_author | submission_date | +—————-+———————-+———————-+————————-+ | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | 5 | 学习 C | FK | 2017-04-05 | +—————-+———————-+———————-+————————-+ 5 rows in set (0.01 sec)

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

+——————-+————————-+————————+ | a.runoob_id | a.runoob_author | b.runoob_count | +——————-+————————-+————————+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +——————-+————————-+————————+ 4 rows in set (0.00 sec)

以上 SQL 语句等价于： mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author; +——————-+————————-+————————+ | a.runoob_id | a.runoob_author | b.runoob_count | +——————-+————————-+————————+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +——————-+————————-+————————+ 4 rows in set (0.01 sec)

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +——————-+————————-+————————+ | a.runoob_id | a.runoob_author | b.runoob_count | +——————-+————————-+————————+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | 5 | FK | NULL | +——————-+————————-+————————+ 5 rows in set (0.01 sec)

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +——————-+————————-+————————+ | a.runoob_id | a.runoob_author | b.runoob_count | +——————-+————————-+————————+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +——————-+————————-+————————+ 5 rows in set (0.01 sec)

  2. <a name="rWj7o"></a>
  3. ## Mysql NULL值得处理

mysql> create table runoob_test_tbl -> ( -> runoob_author varchar(40) NOT NULL, -> runoob_count INT -> ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values (‘RUNOOB’, 20); mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values (‘菜鸟教程’, NULL); mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values (‘Google’, NULL); mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values (‘FK’, 20);

mysql> SELECT * from runoob_test_tbl; +———————-+———————+ | runoob_author | runoob_count | +———————-+———————+ | RUNOOB | 20 | | 菜鸟教程 | NULL | | Google | NULL | | FK | 20 | +———————-+———————+ 4 rows in set (0.01 sec)

以下实例中你可以看到 = 和 != 运算符是不起作用的：

mysql> SELECT FROM runoob_test_tbl WHERE runoob_count = NULL; Empty set (0.00 sec) mysql> SELECT FROM runoob_test_tbl WHERE runoob_count != NULL; Empty set (0.01 sec)

查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL，如下实例：

mysql> SELECT FROM runoob_test_tbl WHERE runoob_count IS NULL; mysql> SELECT from runoob_test_tbl WHERE runoob_count IS NOT NULL;

  2. <a name="SHc3U"></a>
  3. ## select 去重查询

select distinct type from i18n_config

The solution is: [ RowDataPacket { type: ‘ar’ }, RowDataPacket { type: ‘de’ }, RowDataPacket { type: ‘en’ }, RowDataPacket { type: ‘es’ }, RowDataPacket { type: ‘fr’ }, RowDataPacket { type: ‘it’ }, RowDataPacket { type: ‘pt’ }, RowDataPacket { type: ‘th’ }, RowDataPacket { type: ‘vi’ } ]

  1. <a name="C6MPo"></a>
  2. ## MySQL 正则表达式
  3. | 模式 | 描述 |
  4. | --- | --- |
  5. | ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\\n' 或 '\\r' 之后的位置。 |
  6. | $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\\n' 或 '\\r' 之前的位置。 |
  7. | . | 匹配除 "\\n" 之外的任何单个字符。要匹配包括 '\\n' 在内的任何字符，请使用象 '[.\\n]' 的模式。 |
  8. | [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
  9. | [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
  10. | p1&#124;p2&#124;p3 | 匹配 p1 或 p2 或 p3。例如，'z&#124;food' 能匹配 "z" 或 "food"。'(z&#124;f)ood' 则匹配 "zood" 或 "food"。 |
  11. | * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
  12. | + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
  13. | {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
  14. | {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
  16. <a name="RvDdF"></a>
  17. ### 实例
  18. 了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：<br />查找name字段中以'st'为开头的所有数据：

以st开头 mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^st’; 以ok结束 mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘ok$’; 包含mar mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘mar’; 以元音字母开头或ok结尾 mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^[aeiou]|ok$’;

  2. <a name="rlHBg"></a>
  3. ## 创建索引
  4. 索引就相当于图书馆的分类卡片，可以增加查找的速度，但是会降低insert, update, delete操作的时间，索引必须在where的子语句里。
  5. <a name="t238z"></a>
  6. ### 查看与删除唯一索引
  7. ```javascript
  8. show index from table_name;
  9. DROP INDEX [唯一索引名称] ON "表名";

创建唯一索引

  1. ## 创建索引
  2. CREATE INDEX indexName ON mytable(username(length)); 
  3. 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
  4. ## 修改表，添加索引
  5. ALTER table tableName ADD INDEX indexName(columnName)
  6. ## 创建表时，添加索引
  7. CREATE TABLE mytable(  
  8.   ID INT NOT NULL,   
  9.   username VARCHAR(16) NOT NULL,  
  10.   INDEX [indexName] (username(length))   
  11. );  
  12. ## 删除索引
  13. DROP INDEX [indexName] ON mytable; 
  15. ###唯一索引
  16. ### 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，
  17. ### 但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
  19. ## 创建唯一索引
  20. ## unique_md5_index 是索引名称 ，i18n_config是表名，unique_md5是字段名
  21. create unique index unique_md5_index on i18n_config (unique_md5);
  23. ## 修改唯一索引
  24. ALTER table mytable ADD UNIQUE [indexName] (username(length))
  26. ## 创建表时，创建唯一索引
  27. CREATE TABLE mytable(  
  28.   ID INT NOT NULL,   
  29.   username VARCHAR(16) NOT NULL,
  30.   UNIQUE [indexName] (username(length))   
  31. );  
  33. ###使用ALTER 命令添加和删除索引
  34. ##有四种方式来添加数据表的索引：
  36. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 
  37. 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  38. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 
  39. 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
  40. ALTER TABLE tbl_name ADD INDEX index_name (column_list): 
  41. 添加普通索引，索引值可出现多次。
  42. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
  43. 该语句指定了索引为 FULLTEXT ，用于全文索引。
  45. mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
  46. mysql> ALTER TABLE testalter_tbl DROP INDEX c;
  48. ## 显示索引信息
  49. mysql> SHOW INDEX FROM table_name; \G
  50. ........

使用ALTER命令添加字段，删除字段与修改字段

ALTER TABLE table_name ADD column_name datatype comment ‘描述’;

  1. ALTER TABLE table_name ADD column_name datatype comment '描述';
  3. // 注意 ：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。
  4. ALTER TABLE table_name DROP COLUMN column_name; 
  6. ALTER TABLE table_name modify COLUMN column_name datatype;

使用 ALTER 命令添加和删除主键

  1. mysql> ALTER TABLE table_name MODIFY column_name INT NOT NULL;
  3. mysql> ALTER TABLE table_name ADD PRIMARY KEY (column_name);
  5. mysql> ALTER TABLE table_name DROP PRIMARY KEY;

查询表存在不存在

  1. show index from table_name;

求表total合计

  1. select count(id) from tableName;
  2. select count(DISTINCT id) from tableName;
  3. select id, count(*) as count from tableName group by id having count > 1; // 查重

bigInt 超长问题

把超长的字段转换成string

  1. select CONVERT(spu_id, CHAR), xxx_table.* from xxx_table

insert，存在则更新，不存在则新增

SQL中的ON DUPLICATE KEY UPDATE使用详解

注意：想要使用该语法，必须利用“唯一索引”或者“主键索引”，只有产生“索引冲突”，该语法才能正常生效。

  1. CREATE TABLE `testMfc` (                                   
  2.            `id` int(11) NOT NULL AUTO_INCREMENT,                    
  3.            `age` int(11) DEFAULT NULL,                              
  4.            `name` varchar(20) DEFAULT NULL,                         
  5.            `num` int(11) DEFAULT NULL,                              
  6.            PRIMARY KEY (`id`),                                      
  7.            UNIQUE KEY `uk_01` (`age`,`name`)                        
  8.          ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4  
  10. 注意：想要使用该语法，必须利用“唯一索引”或者“主键索引”，只有产生“索引冲突”，该语法才能正常生效。
  12. 此处建立唯一索引：age列和name列
  15. INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  16.   ON DUPLICATE KEY UPDATE c=c+1;
  18. UPDATE t1 SET c=c+1 WHERE a=1;
  20. INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  21.   ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

(1)使用ALTER TABLE语句创建索性

应用于表创建完毕之后再添加。

  1. ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
  2. //普通索引
  3. alter table table_name add index index_name (column_list) ;
  4. //唯一索引
  5. alter table table_name add unique (column_list) ;
  6. //主键索引
  7. alter table table_name add primary key (column_list) ;
  10. 删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：
  11. drop index index_name on table_name ;
  12. alter table table_name drop index index_name ;
  13. alter table table_name drop primary key ;

select where 大小写问题

  1. # 默认情况下，where 是不区分大小写的
  2. select * from tableName where binary name = "Fans"