MYSQL_SQL

MYSQL  SQL语句练习
1、创建数据库：
mysql> CREATE DATABASE IF NOT EXISTS mydata;


2、查看MYSQL 数据库
mysql> SHOW DATABASES;


3、使用mydata数据库
mysql> USE mydata;


4、创建表格：
mysql> CREATE TABLE mytable(
    -> num INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    -> name VARCHAR(50) DEFAULT NULL,
    -> curse VARCHAR(50) DEFAULT NULL,
    -> address VARCHAR(50) DEFAULT NULL)
    -> ENGINE=InnoDB DEFAULT CHARSET=utf8;

5、查看创建的表格结构
mysql> DESC mytable;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| num     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50)      | YES  |     | NULL    |                |
| curse   | varchar(50)      | YES  |     | NULL    |                |
| address | varchar(50)      | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


6、往表格里面插入数据
mysql> INSERT INTO mytable(name,curse,address) VALUES('zhangsanfeng','taijiquan','wudangshan');


7、查看表格mytable里面的内容
mysql> SELECT * FROM mytable;
+-----+--------------+-----------+------------+
| num | name         | curse     | address    |
+-----+--------------+-----------+------------+
|   1 | zhangsanfeng | taijiquan | wudangshan |
+-----+--------------+-----------+------------+
1 row in set (0.00 sec)


8、以mytable为模板创建相同表结构的空表histable
mysql> CREATE TABLE histable LIKE mytable;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from histable;
Empty set (0.00 sec)


mysql> DESC histable;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| num     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50)      | YES  |     | NULL    |                |
| curse   | varchar(50)      | YES  |     | NULL    |                |
| address | varchar(50)      | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


9、以mytable为模板创建表结构和表数据都相同的表hertable
mysql> CREATE TABLE hertable SELECT * FROM mytable;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> SELECT * FROM hertable;
+-----+--------------+-----------+------------+
| num | name         | curse     | address    |
+-----+--------------+-----------+------------+
|   1 | zhangsanfeng | taijiquan | wudangshan |
+-----+--------------+-----------+------------+
1 row in set (0.00 sec)


mysql> DESC hertable;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| num     | int(10) unsigned | NO   |     | 0       |       |
| name    | varchar(50)      | YES  |     | NULL    |       |
| curse   | varchar(50)      | YES  |     | NULL    |       |
| address | varchar(50)      | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


10、删除表
mysql> DROP TABLE hertable;


11、在SHELL中使用SQL语句
[root@mid-22 ~]# mysql -e 'DESC mydata.project;'
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(50)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+


11、利用脚本往数据库表格中插入数据


#!/bin/bash
cat << HELP 
This script is used to insert some data into mysql
HELP
i=0
while [ $i -le 10 ];do
{


        /data/mysql/bin/mysql -e " INSERT INTO mydata.project(name) VALUES('name$i')" &>/root/log_mysql
        let i=i+1
        sleep 1
}
done
echo "INSERT SUCESSFULL"


12、查看插入的数据
mysql> SELECT * FROM project;
+----+--------+
| id | Name   |
+----+--------+
|  1 | name0  |
|  2 | name1  |
|  3 | name2  |
|  4 | name3  |
|  5 | name4  |
|  6 | name5  |
|  7 | name6  |
|  8 | name7  |
|  9 | name8  |
| 10 | name9  |
| 11 | name10 |
+----+--------+
11 rows in set (0.00 sec)


13、使用ALTER给mytable表格增加字段
mysql> ALTER TABLE mytable ADD sex ENUM('M','F') NOT NULL AFTER name;
mysql> ALTER TABLE mytable ADD pro INT UNSIGNED;
mysql> SELECT * FROM mytable;
mysql> SELECT * FROM mytable;
+-----+--------------+-----+-----------+------------+------+
| num | name         | sex | curse     | address    | pro  |
+-----+--------------+-----+-----------+------------+------+
|   1 | zhangsanfeng | M   | taijiquan | wudangshan | NULL |
+-----+--------------+-----+-----------+------------+------+
1 row in set (0.00 sec)


14、使用UPDATE更新表字段的值
mysql> UPDATE mytable SET pro=6 WHERE num=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> SELECT * FROM mytable;
+-----+--------------+-----+-----------+------------+------+
| num | name         | sex | curse     | address    | pro  |
+-----+--------------+-----+-----------+------------+------+
|   1 | zhangsanfeng | M   | taijiquan | wudangshan |    6 |
+-----+--------------+-----+-----------+------------+------+
1 row in set (0.00 sec)


15、批量插入数据
INSERT INTO mytable(name,sex,curse,address,pro) VALUES('linghuchong','M','dugujiujian','huashan','1'),
('huangrong','F','dagoubang','taohuadao','2'),
('xiaolongnv','F','yunvxinjing','gumu','3'),
('qiaofeng','M','xianglongshibazhang','daliao','4'),
('duanyu','M','liumaishenjian','dali','4'),
('xuzhu','M','beimingshengong','shaolinsi','4');


mysql> SELECT * FROM mytable;
+-----+--------------+-----+---------------------+------------+------+
| num | name         | sex | curse               | address    | pro  |
+-----+--------------+-----+---------------------+------------+------+
|   1 | zhangsanfeng | M   | taijiquan           | wudangshan |    6 |
|   2 | guojing      | M   | xianglongshibazhang | taohuadao  |    2 |
|   3 | linghuchong  | M   | dugujiujian         | huashan    |    1 |
|   4 | linghuchong  | M   | dugujiujian         | huashan    |    1 |
|   5 | huangrong    | F   | dagoubang           | taohuadao  |    2 |
|   6 | xiaolongnv   | F   | yunvxinjing         | gumu       |    3 |
|   7 | qiaofeng     | M   | xianglongshibazhang | daliao     |    4 |
|   8 | duanyu       | M   | liumaishenjian      | dali       |    4 |
|   9 | xuzhu        | M   | beimingshengong     | shaolinsi  |    4 |
+-----+--------------+-----+---------------------+------------+------+
9 rows in set (0.00 sec)


16、SELECT语句练习
语法：
SELECT [DISTINCT] column FROM tab_name [WHERE where_condition] [GROUP BY column] [HAVING where_condition] [ORDER BY column] [ASC | DESC][LIMIT {[offset,] row_count}] 


DISTINCT:去重
column为*时，表示全部
WHERE where_condition:限制条件
GROUP BY column：分组
HAVING where_condition：分组后的限制条件
ORDER BY column：排序
ASC | DESC：排序方式，ASC正序，DESC反序
LIMIT {[offset,] row_count} 显示行数，offset表示偏移位，此项不加表示从第一行开始；row_count表示显示的几行


按照pro降序的方式，从第2行开始显示性别为M的，总共显示3行
mysql> SELECT * FROM mytable WHERE sex='M' ORDER BY pro DESC LIMIT 2,3;
统计男女各多少人
mysql> SELECT count(sex) FROM mytable GROUP BY sex;
查询name,address以及其所对应project的Name
mysql> SELECT m.name,m.address,p.Name FROM mytable AS m,project AS p WHERE m.pro=p.id;


左连接：mytable表格中name字段全部显示，project中字段显示对应项，无对应的显示NULL
mysql> SELECT m.name,p.Name FROM mytable AS m LEFT JOIN project AS p ON m.pro=p.id;
+--------------+-------+
| name         | Name  |
+--------------+-------+
| zhangsanfeng | name5 |
| guojing      | name1 |
| linghuchong  | name0 |
| linghuchong  | name0 |
| huangrong    | name1 |
| xiaolongnv   | name2 |
| qiaofeng     | name3 |
| duanyu       | name3 |
| xuzhu        | NULL  |
+--------------+-------+


右连接：
mysql> SELECT m.name,p.Name FROM mytable AS m RIGHT JOIN project AS p ON m.pro=p.id;
+--------------+--------+
| name         | Name   |
+--------------+--------+
| zhangsanfeng | name5  |
| guojing      | name1  |
| linghuchong  | name0  |
| linghuchong  | name0  |
| huangrong    | name1  |
| xiaolongnv   | name2  |
| qiaofeng     | name3  |
| duanyu       | name3  |
| NULL         | name4  |
| NULL         | name6  |
| NULL         | name7  |
| NULL         | name8  |
| NULL         | name9  |
| NULL         | name10 |
+--------------+--------+
14 rows in set (0.00 sec)


用IN表示批量查询：
mysql> SELECT * FROM mytable WHERE name IN ('guojing','huangrong') AND sex='M';
+-----+---------+-----+---------------------+-----------+------+
| num | name    | sex | curse               | address   | pro  |
+-----+---------+-----+---------------------+-----------+------+
|   2 | guojing | M   | xianglongshibazhang | taohuadao |    2 |
+-----+---------+-----+---------------------+-----------+------+


用LIKE进行匹配查找：%匹配任意长度任意字符，_匹配单个字符
mysql> SELECT * FROM mytable WHERE address LIKE 't%';
+-----+-----------+-----+---------------------+-----------+------+
| num | name      | sex | curse               | address   | pro  |
+-----+-----------+-----+---------------------+-----------+------+
|   2 | guojing   | M   | xianglongshibazhang | taohuadao |    2 |
|   5 | huangrong | F   | dagoubang           | taohuadao |    2 |
+-----+-----------+-----+---------------------+-----------+------+


RLIKE支持正则表达式，但此时无法使用索引
mysql> SELECT * FROM mytable WHERE address RLIKE '.*dao$';
+-----+-----------+-----+---------------------+-----------+------+
| num | name      | sex | curse               | address   | pro  |
+-----+-----------+-----+---------------------+-----------+------+
|   2 | guojing   | M   | xianglongshibazhang | taohuadao |    2 |
|   5 | huangrong | F   | dagoubang           | taohuadao |    2 |
+-----+-----------+-----+---------------------+-----------+------+