MySQL

关键字和函数名称必须大写

数据库名称，表名称，字段名称未小写

SQL语句必须以分号结尾

默认端口3306

数据库的基本操作

启动和停止

net stop MySQL57net start MySQL57

登录和退出

mysql -uroot -p123456 -P端口 -h地址 

修改提示符

登陆时修改：

mysql -uroot -p123456 --promat \h

登陆后修改：

PROMPT \u \h \d>

\D完整日期

\d当前数据库

\h服务器名称

\u当前用户

常用命令

显示当前服务器版本

SELECT VERSION();

显示当前日期

SELECT NOW();

显示当前用户

SELECT USER();

创建数据库

CREATE DATABASE / SCHEMA (IF NOT EXISTS) db_name (DEFAULT) CHARACTER SET = charset_name

默认编码为mysql的编码

查看创建建表时候的信息：

SHOW CREATE DATABASE t1;

查看当前服务器下的数据表列表

SHOW DATABASES / SCHEMAS (LINK 'PATTERN' / WHERE expr)

修改数据库

ALTER DATABASE / SCHEMA (db_name) (DEFAULT) CHARACTER SET (=) charset_name

删除数据库

DROP DATABASE / SCHEMA (IF EXISTS) db_name

数据类型与操作数据表

整形型 TINYINT SMALLINT MEDIUMINT INT BIGINT

浮点型 FLOAT[(M,D)] M是数字总位数 D时小数点后面的位数 DOUBLE[(M,D)]

日期时间型 YEAR 1 TIME  3 DATE 3  DATETIME 8 TIMESTAMP 4

字符型 CHAR(M) 等

创建数据表

CREATE TABLE (IF NOT EXISTS)  table_name (column_name data_type, ...)

实例：

CREATE TABLE tb1(    -> username VARCHAR(20),    -> age TINYINT UNSIGNED,    -> salary FLOAT(8,2) UNSIGNED    -> );

查看数据表列表

SHOW TABLES (FROM db_name) (LINKE 'pattern' / WHERE expr)
SHOW TABLES    -> ;+----------------+| Tables_in_test |+----------------+| tb1            |+----------------+1 row in set (0.00 sec)

查看数据表结构

SHOW COLUMNS FROM tb_name

root@localhost test>SHOW COLUMNS FROM tb1    -> ;+----------+---------------------+------+-----+---------+-------+| Field    | Type                | Null | Key | Default | Extra |+----------+---------------------+------+-----+---------+-------+| username | varchar(20)         | YES  |     | NULL    |       || age      | tinyint(3) unsigned | YES  |     | NULL    |       || salary   | float(8,2) unsigned | YES  |     | NULL    |       |+----------+---------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

INSERT插入记录

INSERT tbl_name (col_name, ..) VALUES(val,...)

不写列名的时候插入数据要完全

指定列名的时候可指定插入

root@localhost test>INSERT tb1 VALUES('TOM',25,7863.25);Query OK, 1 row affected (0.02 sec)root@localhost test>INSERT tb1(username,salary) VALUES('John',4500.23);Query OK, 1 row affected (0.00 sec)

SELECT记录查找

SELECT expr,... FROM tbl_name

SELECT * FROM tb1;+----------+------+---------+| username | age  | salary  |+----------+------+---------+| TOM      |   25 | 7863.25 || John     | NULL | 4500.23 |+----------+------+---------+2 rows in set (0.00 sec)

空值与非空

NULL，字段值可以为空

NOT NULL 字段值禁止为空

root@localhost test>CREATE TABLE tb2(    -> username VARCHAR(20) NOT NULL,    -> age TINYINT UNSIGNED NULL    -> );Query OK, 0 rows affected (0.02 sec)

root@localhost test>SHOW COLUMNS FROM tb2;+----------+---------------------+------+-----+---------+-------+| Field    | Type                | Null | Key | Default | Extra |+----------+---------------------+------+-----+---------+-------+| username | varchar(20)         | NO   |     | NULL    |       || age      | tinyint(3) unsigned | YES  |     | NULL    |       |+----------+---------------------+------+-----+---------+-------+2 rows in set (0.00 sec)

自动编号

AUTO_INCREMENT

自动编号，必须和主键组合使用

默认情况下，起始值为1，每次的增量为1

主键约束 PRIMARY KEY

每张数据表只能存在一个主键

主键保证记录的唯一性

主键自动为NOT NULL

root@localhost test>CREATE TABLE tb3(    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(30) NOT NULL    -> );Query OK, 0 rows affected (0.03 sec)

root@localhost test>SHOW COLUMNS FROM TB3;+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(30)          | NO   |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+2 rows in set (0.00 sec)

测试：

root@localhost test>INSERT tb3(username) VALUES('TOM');Query OK, 1 row affected (0.01 sec)root@localhost test>INSERT tb3(username) VALUES('JOHN');Query OK, 1 row affected (0.00 sec)root@localhost test>INSERT tb3(username) VALUES('ROSE');Query OK, 1 row affected (0.00 sec)root@localhost test>INSERT tb3(username) VALUES('Dimitar');Query OK, 1 row affected (0.00 sec)root@localhost test>SELECT * FROM tb3;+----+----------+| id | username |+----+----------+|  1 | TOM      ||  2 | JOHN     ||  3 | ROSE     ||  4 | Dimitar  |+----+----------+4 rows in set (0.00 sec)

唯一约束 UNIQUE KEY

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空值（NULL）

每张数据表可以存在多个唯一约束

默认约束 DEFAULT

默认值

插入记录，如果没有明确为字段赋值，自动赋默认值

试验：

root@localhost test>CREATE TABLE tb6(//建表    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(20) NOT NULL UNIQUE KEY,    -> sex ENUM('1','2','3') DEFAULT '3'//默认为3    -> );Query OK, 0 rows affected (0.00 sec)root@localhost test>SHOW COLUMNS FROM tb6;+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)          | NO   | UNI | NULL    |                || sex      | enum('1','2','3')    | YES  |     | 3       |                |+----------+----------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)root@localhost test>INSERT tb6(username) VALUES('Tom');Query OK, 1 row affected (0.00 sec)root@localhost test>SELECT * FROM tb6;+----+----------+------+| id | username | sex  |+----+----------+------+|  1 | Tom      | 3    |+----+----------+------+1 row in set (0.00 sec)

外键约束

要求：

1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表

2.数据表的存储引擎只能为InnoDB

修改配置文件：default-storage-engine=INNODB

3.外键列和参照列必须具有相似的数据类型

4.外键列和参照列必须创建索引。外键列不存在索引会自动创建

修改数据表

添加单列

ALTER TABLE tbl_name ADD col_name column_definition (FIRSR / AFTER col_name)

默认的添加到最后一行，最后是定义插入位置的，倒数第二个属性是确定要插入的列的信息

mysql> CREATE TABLE users1(    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(20),    -> age TINYINT(3) UNSIGNED    -> );Query OK, 0 rows affected (0.02 sec)mysql> SHOW COLUMNS FROM users1    -> ;+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)          | YES  |     | NULL    |                || age      | tinyint(3) unsigned  | YES  |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

mysql> ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM users1;+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| truename | varchar(20)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)          | YES  |     | NULL    |                || age      | tinyint(3) unsigned  | YES  |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

添加多列

只能在最下方添加

ALTER TABLE tbl_name ADD (COLUMN) (col_name column_definition, ...)

删除列

ALTER　TABLE tbl_name DROP (COLUMN) col_name

mysql> ALTER TABLE users1 DROP truename;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM users1;+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)          | YES  |     | NULL    |                || age      | tinyint(3) unsigned  | YES  |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

添加/删除主键约束

mysql> ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   |     | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | NULL    |       |+----------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

CONSTRAINT PK_users2_id  的作用是为这个主键约束添加一个名字

mysql> ALTER TABLE users2 DROP PRIMARY KEY;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0

不需要添加主键的名字是因为主键约束是唯一的

添加/删除唯一约束

mysql> ALTER TABLE users2 ADD UNIQUE(username);

mysql> SHOW INDEXES FROM users2\G;*************************** 1. row ***************************        Table: users2   Non_unique: 0     Key_name: username Seq_in_index: 1  Column_name: username    Collation: A  Cardinality: 0     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:1 row in set (0.00 sec)ERROR:No query specifiedmysql> ALTER TABLE users2 DROP INDEX username;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   |     | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   |     | NULL    |       || age      | tinyint(3) unsigned  | NO   |     | NULL    |       |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)

添加/删除默认约束

mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   | UNI | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | NULL    |       || age      | tinyint(3) unsigned  | NO   |     | 15      |       |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)

mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   | UNI | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | NULL    |       || age      | tinyint(3) unsigned  | NO   |     | NULL    |       |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)

修改列定义

ALTER　TABLE tb1_name MODIFY  col_name column_definition (first/ AFTER col_name)

修改列名称

ALTER TABLE tbl_name CHANGE old_col_name new_col_name column_definition (FIRST/ALTER col_name)

mysql> ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FORM users2;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORM users2' at line 1mysql> SHOW COLUMNS FROM users2;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   |     | NULL    |       || p_id     | tinyint(3) unsigned  | NO   |     | NULL    |       || id       | smallint(5) unsigned | NO   |     | NULL    |       || age      | tinyint(3) unsigned  | NO   |     | NULL    |       |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)

数据表更名

ALTER TABLE tbl_name RENAME new_tbl_name

RENAME TABLE tb1_name TO new_tb2_name

操作数据表中的元素

插入操作-INSERT

INSERT tbl_name (col_name) VALUES/VALUE 

可以插入表达式或者默认值，用逗号分隔

mysql> CREATE TABLE users(    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> username VARCHAR(20) NOT NULL,    -> password VARCHAR(32) NOT NULL,    -> age TINYINT UNSIGNED NOT NULL DEFAULT 10,    -> sex BOOLEAN    -> );Query OK, 0 rows affected (0.02 sec)mysql> INSERT users VALUES(NULL,'Tom','123',25,1);Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM users;+----+----------+----------+-----+------+| id | username | password | age | sex  |+----+----------+----------+-----+------+|  1 | Tom      | 123      |  25 |    1 |+----+----------+----------+-----+------+1 row in set (0.00 sec)

为自动编号的值赋值，可以用NULL或者DEFAULT

mysql> INSERT users VALUES(DEFAULT,'Tom','123',3*7-5,1),(NULL,'Rose',md5('123'),DEFAULT,0);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  1 | Tom      | 123                              |  25 |    1 ||  2 | Tom      | 123                              |  16 |    1 ||  3 | Rose     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |+----+----------+----------------------------------+-----+------+3 rows in set (0.00 sec)

可以同时插入多个值，并且可以插入函数、表达式等

插入记录-INSERT  SET

mysql> INSERT users SET username='Ben',password='456';Query OK, 1 row affected (0.00 sec)

插入某一列

单表更新-UPDATE

UPDATE (LOW_PRIORITY) (IGNORE) table_reference SET col_name = expr1/DEFAULT ， col_name2 = expr2/DEFAULT

mysql> UPDATE users set age = age +5;Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  1 | Tom      | 123                              |  30 |    1 ||  2 | Tom      | 123                              |  21 |    1 ||  3 | Rose     | 202cb962ac59075b964b07152d234b70 |  15 |    0 ||  4 | Ben      | 456                              |  15 | NULL |+----+----------+----------------------------------+-----+------+4 rows in set (0.00 sec)

可以用where来添加条件

mysql> UPDATE users SET age = age + 10 WHERE id % 2 =0;Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> SELECT * FROM users;+----+----------+----------------------------------+-----+------+| id | username | password                         | age | sex  |+----+----------+----------------------------------+-----+------+|  1 | Tom      | 123                              |  30 |    1 ||  2 | Tom      | 123                              |  31 |    1 ||  3 | Rose     | 202cb962ac59075b964b07152d234b70 |  15 |    0 ||  4 | Ben      | 456                              |  25 | NULL |+----+----------+----------------------------------+-----+------+4 rows in set (0.00 sec)

单表删除-DELETE

DELETE FROM tab_name WHERE。。