<服务>MySQL基础操作
目录：
MySQL启动，关闭和登录
启动命令
[root@why 3306]# /etc/init.d/mysqld start
Starting MySQL.. [确定]
启动基本原理
/etc/init.d/mysqld为一个shell脚本，启动过程中通过调用mysqld_safe脚本，然后mysqld_safe调用mysqld脚本启动mysql
/etc/init.d/mysqld用调用的命令
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
MySQL进程
[root@why ~]# ps -ef | grep mysql | grep -v grep
root 5704 1 0 12:23 pts/6 00:00:00 /bin/sh /usr/local/mysql-5.5.32/bin/mysqld_safe --datadir=/usr/local/mysql-5.5.32/data --pid-file=/usr/local/mysql-5.5.32/data/why.pid
mysql 5945 5704 0 12:23 pts/6 00:00:00 /usr/local/mysql-5.5.32/bin/mysqld --basedir=/usr/local/mysql-5.5.32 --datadir=/usr/local/mysql-5.5.32/data --plugin-dir=/usr/local/mysql-5.5.32/lib/plugin --user=mysql --log-error=/usr/local/mysql-5.5.32/data/why.err --pid-file=/usr/local/mysql-5.5.32/data/why.pid --socket=/usr/local/mysql-5.5.32/tmp/mysql.sock --port=3306
在进程方面由mysqld和mysqld_safe启动两个进程，可以看到mysqld是mysqld_safe的子进程，所以通过/etc/init.d/mysqld和mysqld_safe --user=mysql &的启动实质为一样的。
关闭命令
[root@why 3306]# /etc/init.d/mysqld stop
Shutting down MySQL. [确定]
关闭mysql的实质为kill $mysqld_pid， 强制关闭即为killall mysqld，出现mysqld: no process killed即为完成关闭，尽量不要使用pkill mysqld进行关闭数据库，很有可能造成数据丢失。 也可以通过多实例的mysqladmin -uroot -p shutdown来进行关闭。
MySQL登录
mysql -uroot -p
mysql -uroot -p'123456'
使用第二种方式，明文会被记录到history中，可以通过记录history -c清楚所有或者history -d 指定行号来删除一行，另外mysql命令行中也可以显示这些授权的用户和密码，可以清空/root/.mysql_history中的相关记录。
mysql登录提示符
mysql> prompt \u@why \r:\m:\s->
PROMPT set to '\u@why \r:\m:\s->'
root@why 02:06:22->
不过在退出之后，登录后又会恢复原来的样子，可以通过配置在my.cnf文件的[mysql]中添加prompt \u@why \r:\m:\s->或者自定义的提示符即可，一般我们用作区分生产环境和测试环境。
mysql密码
root密码设置
[root@why /]# /usr/local/mysql/bin/mysqladmin -u root password '123456'
修改root密码
[root@why /]# /usr/local/mysql/bin/mysqladmin -u root -p'123456' password '1234567'
或者在mysql客户端中
mysql> set password=password('12345678')
或
mysql> update mysql.user set password=password(123456789) where user='root' and host='localhost';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 第一种情况如果需要通过-S 指定sock。
- 第二种是修改当前登录用户。
- 第三种情况一般是适合密码丢失后通过--skip-grant-tables参数启动数据库后修改密码。
root密码找回
1.停止mysql
[root@why /]# /etc/init.d/mysqld stop
Shutting down MySQL. [确定]
2.使用--skip-grant-tables参数忽略授权表启动mysql
[root@why /]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql &
[1] 6713
[root@why /]# 161225 15:22:54 mysqld_safe Logging to '/usr/local/mysql-5.5.32/data/why.err'.
161225 15:22:54 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.5.32/data
3.修改密码
mysql> update mysql.user set password=password(123456) where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4.关闭mysql
[root@why /]# /usr/local/mysql/bin/mysqladmin -uroot -p shutdown
Enter password:
161225 15:44:26 mysqld_safe mysqld from pid file /usr/local/mysql-5.5.32/data/why.pid ended
[1]+ Done /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql
5.通过正常的方式启动并登录 6.多实例忽略授权表时通过--defaults-file指定配置文件，登录时通过-S指定sock文件
SQL语句
SQL语句是一种数据库查询和程序设计语言，用于存储数据以及查询、更新和管理关系型数据库。
SQL语句分类
- 数据查询语言(DQL),作用是从表中获取数据，确定数据怎样在应用程序中给出，关键字为SELECT，一般会伴随WHERE(条件)，ORDER BY(排序asc正序，desc为倒序)， GROUP BY(分组)和HAVING等。
- 数据操作语言(DML),通过INSERT，UPDATE和DELETE分别用于添加，修改和删除表中的行数据。
- 事务处理语言(TPL),作用是确保被DML语句影响的所有行及时得以更新，包括BEGIN TRANSACTION，COMMIT和ROLLBACK。特意说一下这个COMMIT，MySQL是默认COMMIT的，Oracle是需要手动COMMIT进行，可以每次十个的提交，这样可以减少磁盘IO。
- 数据控制语言(DCL),通过GRANT和REVOKE进行许可单个用户和用户组对数据库对象的访问。
- 数据定义语言(DDL),通过CREATE和 DROP进行创建表和删除表，为表添加索引等。
- 指针控制语言(CCL),例如DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT等对于一个或多个表单独行进行操作。
一般我运维常用的为数据定义语言(DDL)，数据操作语言(DML)和数据控制语言(DCL)。
创建数据库
mysql> create database why;
Query OK, 1 row affected (0.12 sec)
mysql> show create database why\G
*************************** 1. row ***************************
Database: why
Create Database: CREATE DATABASE `why` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
默认字符集为latin1。
创建非默认字符集数据库
mysql> create database why_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.11 sec)
mysql> create database why_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.16 sec)
CHARACTER SET指定字符集，COLLATE校验规则。可以通过mysql> help create database;进行查看。 我们需要根据企业开发来选定字符集，并确保支持该字符集。
数据库查询
mysql> show databases like '%why%';
+------------------+
| Database (%why%) |
+------------------+
| why |
| why_gbk |
| why_utf8 |
+------------------+
3 rows in set (0.00 sec)
显示当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.15 sec)
删除数据库
mysql> drop database test;
Query OK, 0 rows affected (0.34 sec)
连接数据库
mysql> use why;
Database changed
查看版本，用户和当前时间
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.32 |
+-----------+
1 row in set (0.03 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.09 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2016-12-25 18:54:32 |
+---------------------+
1 row in set (0.06 sec)
删除多余MySQL系统用户
mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.12 sec)
授权用户
mysql> grant all on *.* to 'why'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
授权局域网内主机远程连接数据库
mysql> grant all on *.* to 'why'@'192.168.0.%' identified by '123456';
或
mysql> grant all on *.* to 'why'@'192.168.0.0/24' identified by '123456';
权限收回
mysql> REVOKE INSERT ON *.* FROM 'why'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for why@localhost;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for why@localhost |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'why'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
不过也要注意，取消的权限必须在被授权的范围内，否则不生效，例如收回test.的权限却在取消权限时使用了.*就会不生效。
SELECT(查询), INSERT(插入),UPDATE(更新), DELETE(删除), CREATE(创建), DROP(删除), RELOAD(), SHUTDOWN(), PROCESS(), FILE(), REFERENCES(), INDEX(索引), ALTER(修改索引), SHOW DATABASES(), SUPER(), CREATE TEMPORARY TABLES(创建临时表), LOCK TABLES(锁表), EXECUTE(执行), REPLICATION SLAVE(), REPLICATION CLIENT(), CREATE VIEW(创建视图), SHOW VIEW(展示视图), CREATE ROUTINE(创建存储过程), ALTER ROUTINE(修改存储过程), CREATE USER(创建用户), EVENT(事件), TRIGGER(触发器), CREATE TABLESPACE()
企业环境授权
一般业务只需要SELECT, INSERT,UPDATE和DELETE权限即可，不要过多的去给予权限，又是很多都内部原因导致的生产问题。特殊情况，例如上线新的产品时可以先给予CREATE和DROP权限，在完全上线确保没有这些需求的时候要进行权限的收回。一些清空表也可以通过定时任务mysql -e执行清除缓存表等。
建表语句
mysql> CREATE TABLE subject_comment_manager(
-> subject_comment_manager_id bigint(12) NOT NULL auto_increment COMMENT '主键',
-> subject_type tinyint(2) NOT NULL COMMENT '素材类型',
-> subject_primary_key varchar(255) NOT NULL COMMENT '素材的主键',
-> subject_title varchar(255) NOT NULL COMMENT '素材的名称',
-> edit_user_nick varchar(64) default NULL COMMENT '修改人',
-> edit_user_time timestamp NULL default NULL COMMENT '修改时间',
-> edit_comment varchar(255) default NULL COMMENT '修改理由',
-> state tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常',
-> PRIMARY KEY(subject_comment_manager_id),
-> KEY IDX_PRIMARYKEY (subject_primary_key(32))
-> )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.32 sec)
MySQL5.1及5.1以前版本默认引擎是MyISAM，MySQL5.5.5以后默认引擎是InnoDB。
查询
mysql> desc subject_comment_manager;
+----------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+----------------+
| subject_comment_manager_id | bigint(12) | NO | PRI | NULL | auto_increment |
| subject_type | tinyint(2) | NO | | NULL | |
| subject_primary_key | varchar(255) | NO | MUL | NULL | |
| subject_title | varchar(255) | NO | | NULL | |
| edit_user_nick | varchar(64) | YES | | NULL | |
| edit_user_time | timestamp | YES | | NULL | |
| edit_comment | varchar(255) | YES | | NULL | |
| state | tinyint(1) | NO | | 1 | |
+----------------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> show create table subject_comment_manager\G
*************************** 1. row ***************************
Table: subject_comment_manager
Create Table: CREATE TABLE `subject_comment_manager` (
`subject_comment_manager_id` bigint(12) NOT NULL AUTO_INCREMENT COMMENT '主键',
`subject_type` tinyint(2) NOT NULL COMMENT '素材类型',
`subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键',
`subject_title` varchar(255) NOT NULL COMMENT '素材的名称',
`edit_user_nick` varchar(64) DEFAULT NULL COMMENT '修改人',
`edit_user_time` timestamp NULL DEFAULT NULL COMMENT '修改时间',
`edit_comment` varchar(255) DEFAULT NULL COMMENT '修改理由',
`state` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0代表关闭，1代表正常',
PRIMARY KEY (`subject_comment_manager_id`),
KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
创建索引和主键
索引建立在字段之上，然后以索引列为查询条件时可以查询数据的速度。无论是主键索引还是列索引都要在表的对应列上创建，可以对单列创建索引，也可以对多列索引。
mysql> create table student(
-> id int(4) NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id),
-> KEY index_name (name)
-> );
Query OK, 0 rows affected (0.18 sec)
AUTO_INCREMENT自增，primary key(id)主键为id，index_name普通索引。
添加主键
mysql> create table student(
-> id int(4) NOT NULL ,
-> name char(20) NOT NULL,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> KEY index_name (name)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> alter table student change id id int primary key auto_increment;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
普通索引
mysql> alter table student drop index index_name;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table student add index index_name(name);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
通过对字段的前N个字符创建索引
mysql> create index index_dept on student(dept(8));
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | index_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | index_dept | 1 | dept | A | 0 | 8 | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
联合索引
mysql> create index index_name_dept on student(name,dept);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | index_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | index_dept | 1 | dept | A | 0 | 8 | NULL | YES | BTREE | | |
| student | 1 | index_name_dept | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | index_name_dept | 2 | dept | A | 0 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
联合索引效率会更高一些，但是占用的资源也会更高。 联合索引的生效条件，举个栗子，当创建的索引为index(a,b,c)仅a，ab，abc三个查询条件可以走索引，b，bc都不行，这是索引的前缀生效特性。
唯一索引
mysql> create unique index uni_index_age on student(age);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | UNI | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
唯一索引一般是一个约束条件，例如我们注册一些网站等用户，需要手机号，这个手机号一般就是唯一索引，当我们再次以上次注册手机号进行注册时，就没有办法进行注册了。
索引列的创建及生效条件
索引创建确实可以提高查询效率，但是索引也有缺点，例如占用系统空间，更新数据库时还需要维护索引数据的，就好比我们的文档目录，写在开头但是会占用一些页面，如果添加一个目录，就需要对目录进行更新，如果数百行的小表，写频繁，读少的业务要减少索引。 索引应该创建在经常在where后的条件列上，一般都选择唯一值多的大表上建立索引，像性别列应该是没有人建索引。
插入命令
mysql> insert into student(id,name,age,dept) values (1,'why',12,'dba');
Query OK, 1 row affected (0.11 sec)
mysql> select * from student;
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
| 1 | why | 12 | dba |
+----+------+-----+------+
1 row in set (0.00 sec)
# 因为主键是自增，可以不添加主键
mysql> insert into student(name,age,dept) values ('mabiao',13,'dev');
Query OK, 1 row affected (0.03 sec)
mysql> select * from student;
+----+--------+-----+------+
| id | name | age | dept |
+----+--------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
+----+--------+-----+------+
2 rows in set (0.00 sec)
# 不指定列就需要插入的值要与列一一对应并且数据类型也要正确
mysql> insert into student values (3,'yanwei',15,'dev');
Query OK, 1 row affected (0.02 sec)
mysql> select * from student;
+----+--------+-----+------+
| id | name | age | dept |
+----+--------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
| 3 | yanwei | 15 | dev |
+----+--------+-----+------+
3 rows in set (0.00 sec)
# 插入多条，一般推荐这种多条插入的方式，可以减少磁盘的开销，提升效率
mysql> insert into student values (4,'liuyiping',16,'ccie'),(5,'chenyu',11,'ops');
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
查询语句
mysql> select * from student;
+----+-----------+-----+------+
| id | name | age | dept |
+----+-----------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
| 3 | yanwei | 15 | dev |
| 4 | liuyiping | 16 | ccie |
| 5 | chenyu | 11 | ops |
+----+-----------+-----+------+
5 rows in set (0.00 sec)
mysql> select id,name,age,dept from student;
+----+-----------+-----+------+
| id | name | age | dept |
+----+-----------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
| 3 | yanwei | 15 | dev |
| 4 | liuyiping | 16 | ccie |
| 5 | chenyu | 11 | ops |
+----+-----------+-----+------+
5 rows in set (0.00 sec)
第二种会比第一种查询效率要快，
我们可以指定条件
mysql> select id,name,age,dept from student where id=1;
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
| 1 | why | 12 | dba |
+----+------+-----+------+
1 row in set (0.01 sec)
mysql> select id,name,age,dept from student where id>2;
+----+-----------+-----+------+
| id | name | age | dept |
+----+-----------+-----+------+
| 3 | yanwei | 15 | dev |
| 4 | liuyiping | 16 | ccie |
| 5 | chenyu | 11 | ops |
+----+-----------+-----+------+
3 rows in set (0.00 sec)
mysql> select id,name,age,dept from student where id>4 or id<2;
+----+--------+-----+------+
| id | name | age | dept |
+----+--------+-----+------+
| 1 | why | 12 | dba |
| 5 | chenyu | 11 | ops |
+----+--------+-----+------+
2 rows in set (0.00 sec)
mysql> select id,name,age,dept from student limit 2;
+----+--------+-----+------+
| id | name | age | dept |
+----+--------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
+----+--------+-----+------+
2 rows in set (0.00 sec)
mysql> select id,name,age,dept from student order by id limit 2;
+----+--------+-----+------+
| id | name | age | dept |
+----+--------+-----+------+
| 1 | why | 12 | dba |
| 2 | mabiao | 13 | dev |
+----+--------+-----+------+
2 rows in set (0.00 sec)
mysql> select id,name,age,dept from student order by id desc limit 2;
+----+-----------+-----+------+
| id | name | age | dept |
+----+-----------+-----+------+
| 5 | chenyu | 11 | ops |
| 4 | liuyiping | 16 | ccie |
+----+-----------+-----+------+
2 rows in set (0.00 sec)
默认为升序acs
联表查询
查看SQL语句执行计划
mysql> explain select id,name,age,dept from student where name='why'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE #查询类型
table: student #查询表
type: ref #
possible_keys: index_name,index_name_dept #可能使用的索引
key: index_name #使用的索引
key_len: 60 #索引长度
ref: const #
rows: 1 #扫描行数
Extra: Using where
1 row in set (0.00 sec)
修改数据
mysql> update student set name='anxiang' where id=5;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
删除数据
mysql> delete from student where name = 'why';
Query OK, 1 row affected (0.09 sec)
清空数据
mysql> delete from student;
mysql> truncate table student;
相比第一种，第二种更快，因为delete是一行一行删，truncate是直接删除数据文件。
修改表的字段
alter table 表明 add 字段 类型 其他；
mysql> alter table student add sex char(4);
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | UNI | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
| sex | char(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.07 sec)
mysql> alter table student add tel char(11) not null after age;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | UNI | 0 | |
| tel | char(11) | NO | | NULL | |
| dept | varchar(16) | YES | MUL | NULL | |
| sex | char(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
如果要放在第一列就用first，这样first和after就能满足所有需求了。
改变字段
alter change和alter modify都行
mysql> alter table student change tel telnumber char(11) after age;
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | UNI | 0 | |
| telnumber | char(11) | YES | | NULL | |
| dept | varchar(16) | YES | MUL | NULL | |
| sex | char(4) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> alter table student modify telnumber char(12) after age;
Query OK, 5 rows affected (0.15 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | UNI | 0 | |
| telnumber | char(12) | YES | | NULL | |
| dept | varchar(16) | YES | MUL | NULL | |
| sex | char(4) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.05 sec)
企业删除数据的方式
一般程序是不会用删除，表中会有一个状态码字段，删除时就会update状态码，程序读取时会对状态码做判断，确定这条时候存在
更改表名
mysql> rename table student to man;
Query OK, 0 rows affected (0.03 sec)
mysql> alter table man rename to student;
Query OK, 0 rows affected (0.01 sec)
