ubuntu下mysql sql 语句 汇总

——待续

1.登陆

mysql -u root -p

2. 创建，删除,备份数据库

show databases;
create database db_name;
drop database db_name;

使用：use db_name

备份：./bakmysql.sh

3. 创建表，拷贝表结构（部分/全部），查看表结构，删除表

mysql> create table test(
    -> id integer primary key not null,
    -> name varchar(30),
    -> sex varchar(4),
    -> scorelist_id integer);
Query OK, 0 rows affected (0.19 sec)

mysql> desc test;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   | PRI | NULL    |       |
| name         | varchar(30) | YES  |     | NULL    |       |
| sex          | varchar(4)  | YES  |     | NULL    |       |
| scorelist_id | int(11)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> create table test1 as select * from test where 1=0;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| name         | varchar(30) | YES  |     | NULL    |       |
| sex          | varchar(4)  | YES  |     | NULL    |       |
| scorelist_id | int(11)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> create table test2 as select id, name from test where 1=0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> drop table test2;

4. 基本修改表内容——插入记录，插入列，更新记录，删除记录，删除列（不允许）

mysql> insert into test values(1,'lilei','m',1);
mysql> alter table test add column grade char(2);
update test set grade = '1' where id=1;
mysql> select * from test;
+----+-------+------+--------------+-------+
| id | name  | sex  | scorelist_id | grade |
+----+-------+------+--------------+-------+
|  1 | lilei | m    |            1 | 1     |
+----+-------+------+--------------+-------+

mysql> delete from score where scorelist_id=5;

5. 基本查询表内容——全部查询，分组查询(having, min, max, count, sum, avg)，排序查询(asc, desc, limit)，嵌套查询

mysql> select * from test;
+----+-----------+------+--------------+-------+
| id | name      | sex  | scorelist_id | grade |
+----+-----------+------+--------------+-------+
|  1 | lilei     | m    |            1 | 1     |
|  2 | nick      | m    |            2 | 2     |
|  3 | hanmeimei | f    |            3 | 1     |
|  4 | vion      | f    |            4 | 2     |
+----+-----------+------+--------------+-------+
4 rows in set (0.00 sec)

mysql> select * from score;
+--------------+-------+
| scorelist_id | score |
+--------------+-------+
|            1 |    90 |
|            2 |    70 |
|            3 |    80 |
|            4 |    95 |
+--------------+-------+
4 rows in set (0.00 sec)

mysql> select * from score order by score desc limit 2;
+--------------+-------+
| scorelist_id | score |
+--------------+-------+
|            4 |    95 |
|            1 |    90 |
+--------------+-------+
2 rows in set (0.00 sec)

mysql> select avg(score) from test,score where test.scorelist_id = score.scorelist_id group by test.grade;
+------------+
| avg(score) |
+------------+
|    85.0000 |
|    82.5000 |
+------------+

mysql> select name from test where scorelist_id in (select scorelist_id from score where score = (select max(score) from score));
+------+
| name |
+------+
| vion |
+------+

注：mysql不支持with 子句

mysql> select * from test;
+----+-----------+------+--------------+-------+
| id | name      | sex  | scorelist_id | grade |
+----+-----------+------+--------------+-------+
|  1 | lilei     | m    |            1 | 1     |
|  2 | nick      | m    |            2 | 2     |
|  3 | hanmeimei | f    |            3 | 1     |
|  4 | vion      | f    |            4 | 2     |
|  5 | lilei     | f    |            5 | 2     |
+----+-----------+------+--------------+-------+
mysql> select * from test group by sex having count(*)>1;
+----+-----------+------+--------------+-------+
| id | name      | sex  | scorelist_id | grade |
+----+-----------+------+--------------+-------+
|  3 | hanmeimei | f    |            3 | 1     |
|  1 | lilei     | m    |            1 | 1     |
+----+-----------+------+--------------+-------+
2 rows in set (0.00 sec)

6. 复杂查询——union, intersect, except,  select as(with)

mysql> select * from test;
+----+-----------+------+--------------+-------+
| id | name      | sex  | scorelist_id | grade |
+----+-----------+------+--------------+-------+
|  1 | lilei     | m    |            1 | 1     |
|  2 | nick      | m    |            2 | 2     |
|  3 | hanmeimei | f    |            3 | 1     |
|  4 | vion      | f    |            4 | 2     |
|  5 | lilei     | f    |            5 | 2     |
+----+-----------+------+--------------+-------+
5 rows in set (0.05 sec)

mysql> (select name from test where grade='1') union all (select name from test where grade='2');
+-----------+
| name      |
+-----------+
| lilei     |
| hanmeimei |
| nick      |
| vion      |
| lilei     |
+-----------+
5 rows in set (0.00 sec)

mysql> (select name from test where grade='1') union (select name from test where grade='2');
+-----------+
| name      |
+-----------+
| lilei     |
| hanmeimei |
| nick      |
| vion      |
+-----------+
4 rows in set (0.00 sec)

intersect等效用法：

mysql> select A.name from (select name from test where grade='2') as A, (select name from test where grade='1') as B where A.name=B.name;
+-------+
| name  |
+-------+
| lilei |
+-------+

mysql> select * from test;
+----+-----------+------+--------------+-------+
| id | name      | sex  | scorelist_id | grade |
+----+-----------+------+--------------+-------+
|  1 | lilei     | m    |            1 | 1     |
|  2 | nick      | m    |            2 | 2     |
|  3 | hanmeimei | f    |            3 | 1     |
|  4 | vion      | f    |            4 | 2     |
|  5 | lilei     | f    |            5 | 2     |
+----+-----------+------+--------------+-------+
5 rows in set (0.00 sec)

mysql> select name from test where name not in (select name from test where grade='2');
+-----------+
| name      |
+-----------+
| hanmeimei |
+-----------+
1 row in set (0.00 sec)

mysql> select name from test where name not in (select name from test where grade='1');
+------+
| name |
+------+
| nick |
| vion |
+------+