数据库常用的sql语句汇总

连接mysql

mysql -u root -p

Enter password:*******

输入密码，进入mysql，若密码为空 则直接回车

exit  #退出

---

操作数据库

use mysql;  # 使用数据库

show databases; #查看数据库 

mysqladmin -u root -p drop Ybh-mall_test   #在终端删除数据库

create database YBH_mall_test;  #创建数据库
drop database YBH_mall_test;    #删除数据库

数据库名不可以使用 - (短横杠)

---

操作数据库中的表

## 创建数据表

 create table teachers (
    -> id int not null auto_increment,
    -> name varchar(100),
    -> primary key (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

# 查看有哪些表

mysql> show tables;

+--------------------+
| Tables_in_zyr_test |
+--------------------+
| teachers           |
+--------------------+

1 row in set (0.00 sec)

## 查看表中内容

mysql> desc teachers;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

#在数据表中插入数据

mysql> insert into teachers (id, name) values (1, "lala");
Query OK, 1 row affected (0.01 sec)

#读取数据表全部字段

mysql> select * from teachers;

+----+------+
| id | name |
+----+------+
|  1 | lala |
+----+------+

1 row in set (0.00 sec)


#读取数据表指定字段
mysql> select name from teachers;
   
+------+
| name |
+------+
| lala |
+------+
1 row in set (0.00 sec)

# 给teachers数据表增加字段

mysql> alter table teachers add age varchar(100);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

# where条件查询

mysql> select * from teachers where name = "ww";

+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | ww   | qwe  |
+----+------+------+
1 row in set (0.00 sec)

# 更新表中字段
mysql> update teachers set age="123" where id=2;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


# 查询显示更新成功
mysql> select * from teachers;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lala | NULL |
|  2 | ww   | 123  |
+----+------+------+
2 rows in set (0.00 sec)

#删除某条数据
mysql> delete from teachers where id=1;
Query OK, 1 row affected (0.00 sec)


#查询 删除成功
mysql> select * from teachers;

+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | ww   | 123  |
+----+------+------+
1 row in set (0.00 sec)

# id逆序排列，返回数据
mysql> select * from teachers order by id desc;

+----+------+------+
| id | name | age  |
+----+------+------+
|  6 | xx   | 44   |
|  5 | zz   | 33   |
|  4 | aa   | 22   |
|  3 | qq   | 11   |
|  2 | ww   | 123  |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from teachers;

+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | ww   | 123  |
|  3 | qq   | 11   |
|  4 | aa   | 22   |
|  5 | zz   | 33   |
|  6 | xx   | 44   |
|  7 | xx   | 44   |
|  8 | zz   | 44   |
|  9 | zz   | 44   |
| 10 | qq   | 44   |
+----+------+------+
9 rows in set (0.00 sec)


# 以name字段 分组查询
mysql> select name, count(*) from teachers group by name;
+------+----------+
| name | count(*) |
+------+----------+
| ww   |        1 |
| qq   |        2 |
| aa   |        1 |
| zz   |        3 |
| xx   |        2 |
+------+----------+
5 rows in set (0.01 sec)

# like 字句查询  like相当于等号
mysql> select * from teachers where name like "xx" and age="44";

+----+------+------+
| id | name | age  |
+----+------+------+
|  6 | xx   | 44   |
|  7 | xx   | 44   |
+----+------+------+
2 rows in set (0.01 sec)

# teachers 表内容
mysql> select * from teachers
    -> ;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | ww   | 123  |
|  3 | qq   | 11   |
|  4 | aa   | 22   |
|  5 | zz   | 33   |
|  6 | xx   | 44   |
|  7 | xx   | 44   |
|  8 | zz   | 44   |
|  9 | zz   | 44   |
| 10 | qq   | 44   |
+----+------+------+
9 rows in set (0.00 sec)

# students 表内容
mysql> select * from students;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | ww   | 123 |
|  2 | xx   | 44  |
|  3 | zz   | 44  |
+----+------+-----+
3 rows in set (0.00 sec)


# 使用union 查询  将2张表组合为一张表

mysql> select name from teachers
    -> union
    -> select name from students;
    
+------+
| name |
+------+
| ww   |
| qq   |
| aa   |
| zz   |
| xx   |
+------+
5 rows in set (0.00 sec)

未完待续…