Mysql 常用命令
一、Mysql 创建数据库
mysql> create database study charset utf8; Query OK, 1 row affected (0.01 sec)
二、创建数据表
语法：
CREATE TABLE table_name (column_name column_type);
创建一个student表
mysql> create table student(
-> stu_id int NOT NULL AUTO_INCREMENT,
-> name char(32) NOT NULL,
-> age int NOT NULL,
-> register_date DATE NOT NULL,
-> primary key (stu_id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc student;
+---------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| register_date | date | NO | | NULL | |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
- AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
三、数据操作
3.1 插入数据
语法：
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
插入数据：
mysql> insert into student (name,age,register_date) values ('bigberg',11,'2018-01-01');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+--------+---------+-----+---------------+
| stu_id | name | age | register_date |
+--------+---------+-----+---------------+
| 1 | bigberg | 11 | 2018-01-01 |
+--------+---------+-----+---------------+
1 row in set (0.00 sec)
3.2 查询数据
语法：
SELECT column_name,column_name FROM table_name [WHERE Clause] [OFFSET M ][LIMIT N]
- 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
- SELECT 命令可以读取一条或者多条记录。
- 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0， 如果使用不能单独使用。
- 你可以使用 LIMIT 属性来设定返回的记录数。
select * from student; select * from student where stu_id > 2; select * from student where register_date like '2018-02-%';
mysql> select * from student limit 3, 1; +--------+--------+-----+---------------+ | stu_id | name | age | register_date | +--------+--------+-----+---------------+ | 4 | wangwu | 23 | 2018-02-14 | +--------+--------+-----+---------------+ 1 row in set (0.00 sec) # limit后面是从第3条开始读，读取1条信息。
mysql> select * from student limit 3 offset 2; +--------+--------+-----+---------------+ | stu_id | name | age | register_date | +--------+--------+-----+---------------+ | 3 | lisi | 31 | 2018-02-11 | | 4 | wangwu | 23 | 2018-02-14 | +--------+--------+-----+---------------+ 2 rows in set (0.00 sec) # limit后面跟的是3条数据，offset后面是从第3条开始读取
3.3 where 字句
语法：
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 |
| <>, != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 |
| > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
| < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
| >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
| <= | 小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
使用主键来作为 WHERE 子句的条件查询是非常快速的。
select * from student where register_date > '2016-03-04';
3.4 UPDATE 查询
语法：
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
更新数据：
mysql> update student set age=22 where stu_id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
3.5 DELETE 语句
语法：
DELETE FROM table_name [WHERE Clause]
删除语句：
mysql> delete from student where stu_id = 4; Query OK, 1 row affected (0.00 sec)
3.6 order by排序
语法：
SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]
排序语句：
# 使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。 mysql> select name, age, register_date from student order by age DESC; +---------+-----+---------------+ | name | age | register_date | +---------+-----+---------------+ | lisi | 31 | 2018-02-11 | | bigberg | 22 | 2018-01-01 | | zhansan | 21 | 2018-01-11 | +---------+-----+---------------+ 3 rows in set (0.00 sec)
mysql> select name, age, register_date from student order by age; +---------+-----+---------------+ | name | age | register_date | +---------+-----+---------------+ | zhansan | 21 | 2018-01-11 | | bigberg | 22 | 2018-01-10 | | wangwu | 22 | 2018-01-02 | | lisi | 31 | 2018-02-11 | +---------+-----+---------------+ 4 rows in set (0.00 sec) mysql> select name, age, register_date from student order by age, register_date; +---------+-----+---------------+ | name | age | register_date | +---------+-----+---------------+ | zhansan | 21 | 2018-01-11 | | wangwu | 22 | 2018-01-02 | | bigberg | 22 | 2018-01-10 | | lisi | 31 | 2018-02-11 | +---------+-----+---------------+ 4 rows in set (0.00 sec)
3.7 group by 分组
语法：
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
group by 语句
mysql> select age,count(*) as number from student group by age; +-----+--------+ | age | number | +-----+--------+ | 21 | 1 | | 22 | 2 | | 31 | 1 | +-----+--------+ 3 rows in set (0.00 sec)
3.8 ALTER命令
语法：
# 我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令 alter table student drop register_date; #从student表删除register_date 字段 alter table student add phone int(11) not null; ＃添加phone字段
增加和删除字段
# 增加一个性别字段
mysql> alter table student add gender enum('M','F') NOT NULL;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除age字段
mysql> alter table student drop age;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+--------+---------+---------------+--------+
| stu_id | name | register_date | gender |
+--------+---------+---------------+--------+
| 1 | bigberg | 2018-01-10 | M |
| 2 | zhansan | 2018-01-11 | M |
| 3 | lisi | 2018-02-11 | M |
| 5 | wangwu | 2018-01-02 | M |
| 6 | Lily | 2018-02-23 | F |
+--------+---------+---------------+--------+
5 rows in set (0.00 sec)
修改字段类型
mysql> DESC student;
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| register_date | date | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
+---------------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table student modify name varchar(50);
Query OK, 5 rows affected (0.11 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| register_date | date | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
+---------------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
修改表名
mysql> alter table student rename to student_table; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-----------------+ | Tables_in_study | +-----------------+ | student_table | +-----------------+ 1 row in set (0.00 sec)
修改字段名称
语法： ALTER TABLE table_name change column_name new_name 数据类型 --- alter table user change passwd password varchar(64);
