mysql常用基本命令的使用

来源：互联网发布：手机如何设置网络共享编辑：程序博客网时间：2024/06/09 19:28

1.创建名为mycdb的数据库：

a.linux中直接从bash创建：

[root@test~]#mysqladmin -u root -p create mycdb

b.进入mysql数据库后

mysql> create database mycdb;

2.查看数据库：

3.删除mycdb这个数据库

a.linux中直接由bash中删除：

[root@test~]#mysqladmin -u root -p drop mycdb

b.进入mysql中删除：

mysql> drop database mycdb;
Query OK, 0 rows affected (0.00 sec)

4.选择mycdb这个数据库：

mysql> use mycdb;
Database changed

5.创建名为person的数据表

mysql> create table person(name VARCHAR(10),sex VARCHAR(1),age VARCHAR(10));

Query OK, 0 rows affected (1.55 sec)

6.查看数据库mycdb中的数据表:

7.删除数据库mycdb中的数据表mycdb：

mysql> drop table mycdb;
Query OK, 0 rows affected (0.84 sec)

8.向person这个数据表中插入数据：

mysql> insert into person(name,sex,age) values('jay','m',35);
Query OK, 1 row affected (0.24 sec)

9.读取person数据表的内容：

mysql> select * from person;
+---------+------+------+
| name | sex | age |
+---------+------+------+
| jay | m | 35 |
| eason | m | 38 |
| naying | f | 42 |
| liuhuan | m | 52 |
+---------+------+------+
4 rows in set (0.00 sec)

10.如何利用where：

mysql> select * from person where name='liuhuan';
+---------+------+------+
| name | sex | age |
+---------+------+------+
| liuhuan | m | 52 |
+---------+------+------+
1 row in set (0.00 sec)

11.利用update修改person数据表中 name=‘eason’ 的数据age=43

mysql> update person set age=43 where name='eason';
Query OK, 1 row affected (0.27 sec)
Rows matched: 1 Changed: 1 Warnings: 0

12.利用delete删除person数据表中 name=‘wangfeng’ 的数据

mysql> delete from person where name='wangfeng';
Query OK, 1 row affected (0.24 sec)

13.mysql中的like语句，这个语句使用 % 表示任意字符，若没有 % 则如同 = 一样，可以用or或and连接一个或多个条件：

a.无 % 时候如 = ,选取person数据表中sex为‘m’的数据：

mysql> select * from person where sex like 'm';
+---------+------+------+
| name | sex | age |
+---------+------+------+
| jay | m | 35 |
| eason | m | 43 |
| liuhuan | m | 52 |
+---------+------+------+
3 rows in set (0.00 sec)

b.有 % 时匹配多个任意字符，选取person中name含有‘o’的数据：

mysql> select * from person where name like '%i%';
+---------+------+------+
| name | sex | age |
+---------+------+------+
| naying | f | 42 |
| liuhuan | m | 52 |
+---------+------+------+
2 rows in set (0.00 sec)

14. mysql中union操作符；

union操作符用于对两个及以上的select句子进行连接并整合到一个结果中；

union有两个参数可选（all、distinct）；distinct（默认的）：多个select句子中有相同的数据会默认删除重复的数据再整合到一个结果；all：返回所有结果，包括重复的；

再创建一个名为sing的数据表

mysql> insert into sing(name,sex,age) values('eason','m',43);

mysql> show tables;
+-----------------+
| Tables_in_mycdb |
+-----------------+
| person |
| sing |
+-----------------+

mysql> select * from sing;
+------+------+------+
| name | sex | age |
+------+------+------+
| lx | m | 12 |

| eason | m | 43 |
| gh | f | 32 |
| jkl | m | 41 |
| og | f | 22 |
+------+------+------+

mysql> select * from person;
+---------+------+------+
| name | sex | age |
+---------+------+------+
| jay | m | 35 |
| eason | m | 43 |
| naying | f | 42 |
| liuhuan | m | 52 |
+---------+------+------+
4 rows in set (0.00 sec)

mysql> select * from person union select * from sing;
+---------+------+------+
| name | sex | age |
+---------+------+------+
| jay | m | 35 |
| eason | m | 43 |
| naying | f | 42 |
| liuhuan | m | 52 |
| lx | m | 12 |

| gh | f | 32 |
| jkl | m | 41 |
| og | f | 22 |

+---------+------+------+
8 rows in set (0.00 sec)

a.默认筛选出person中sex=‘m’和sing中sex=‘m’的数据；

mysql> select * from person where sex='m' union select * from sing where sex='m';

+---------+------+------+
| name | sex | age |
+---------+------+------+
| jay | m | 35 |
| eason | m | 43 |
| liuhuan | m | 52 |
| lx | m | 12 |
| jkl | m | 41 |
+---------+------+------+
5 rows in set (0.00 sec)

b.使用union all来筛选：

mysql> select * from person where sex='m' union all select * from sing where sex='m';
+---------+------+------+
| name | sex | age |
+---------+------+------+
| jay | m | 35 |
| eason | m | 43 |
| liuhuan | m | 52 |
| lx | m | 12 |
| jkl | m | 41 |
| eason | m | 43 |
+---------+------+------+
6 rows in set (0.00 sec)

阅读全文

0 0