MySql简单使用

在终端中操作数据库

Linux中Mysql是区分大小写的，win不区分大小写

数据库

1. show databases查看数据库服务器中数据库

输入命令行后需要输入;作为结束

2. use <databasesname>选择一个数据库进行操作

mysql> use test
Database changed

3. exit退出数据服务器

4.create database <name>数据库服务器中创建数据库

mysql> create database study
    -> ;
Query OK, 1 row affected (0.05 sec)
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| study              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

5. show tables查看数据库中的数据表

mysql> show tables
    -> ;
Empty set (0.00 sec)

mysql>

6. drop database<datebasename>删除数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| study              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| study              |
+--------------------+
3 rows in set (0.00 sec)

数据表

1. create table <tablename>(datename datatype(size)，...)创建数据表

表名字中参数为原始名和元素大小

mysql> create table pet(name varchar(20), owner varchar(20), species varchar(20), sex char(1), birth date, death date);
Query OK, 0 rows affected (0.31 sec)

mysql> show tables
    -> ;
+-----------------+
| Tables_in_study |
+-----------------+
| pet             |
+-----------------+
1 row in set (0.00 sec)

如何设置字段不能为NULL

1. 创建时：

在字段类型之后加上NOT NULL即可
create table pet(name varchar(20) NOT NULL)

2.describe <tablename>查看数据表的结构

mysql> describe pet
    -> ;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.03 sec)

• Filed 字段名
• Type 字段类型
• Null 是否可以为空
• Key 约束条件
• Default 默认值
• Extra 额外的

3. select <Field> from <tablename>查看数据表中的记录

Field表示字段名字。通过这个参数可以进行筛选出需要的子段。
*是全部字段名

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

select 添加查找条件

mysql> select * from pet where name = "Tom" and sex = "f";
+------+-------+---------+------+------------+-------+
| name | owner | species | sex  | birth      | death |
+------+-------+---------+------+------------+-------+
| Tom  | Diane | ham     | f    | 1999-03-30 | NULL  |
+------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

限制查找结果的个数limit x

mysql> select * from user limit 2
    -> ;
+----------+--------+
| username | pass   |
+----------+--------+
| dawd     | 213123 |
| dokpd    | 213123 |
+----------+--------+
2 rows in set (0.02 sec)

mysql> select * from user limit 1;
+----------+--------+
| username | pass   |
+----------+--------+
| dawd     | 213123 |
+----------+--------+
1 row in set (0.01 sec)

4.insert into <tablename>往数据表中添加数据记录

mysql> insert into pet
    -> values ("Tom", "Diane", "ham", "f", "1999-03-30",NULL);
Query OK, 1 row affected (0.11 sec)

输入命令行后需要补齐输入的信息 value (. . .)

mysql> select * from pet
    -> ;
+------+-------+---------+------+------------+-------+
| name | owner | species | sex  | birth      | death |
+------+-------+---------+------+------------+-------+
| Tom  | Diane | ham     | f    | 1999-03-30 | NULL  |
+------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

5. drop table <tablename>删除数据表

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| pet             |
| tmp             |
+-----------------+
2 rows in set (0.00 sec)

mysql> drop table tmp;
Query OK, 0 rows affected (0.65 sec)

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| pet             |
+-----------------+
1 row in set (0.00 sec)

6. update tablename set Field = newValue where conditon...更新满足条件的值

mysql> select * from pet
    -> ;
+------+-------+---------+------+------------+-------+
| name | owner | species | sex  | birth      | death |
+------+-------+---------+------+------------+-------+
| Tom  | Diane | ham     | f    | 1999-03-30 | NULL  |
| Jack | Diane | ham     | f    | 1999-03-30 | NULL  |
+------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> update pet set name = "Li" where name = "Tom";
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet
    -> ;
+------+-------+---------+------+------------+-------+
| name | owner | species | sex  | birth      | death |
+------+-------+---------+------+------------+-------+
| Li   | Diane | ham     | f    | 1999-03-30 | NULL  |
| Jack | Diane | ham     | f    | 1999-03-30 | NULL  |
+------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)

7. delete from <tablename>清除数据表中的所有记录

mysql> select * from user;
+----------+------+
| username | pass |
+----------+------+
| 21344    | 1234 |
| Tom      | 1234 |
+----------+------+
2 rows in set (0.00 sec)

mysql> delete from user;
Query OK, 2 rows affected (0.02 sec)

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

条件删除

mysql> select * from user;
+----------+--------+
| username | pass   |
+----------+--------+
| awdad    | 1234   |
| dawd     | 213123 |
| dawda    | 1234   |
| dokpd    | 213123 |
| jack     | 1234   |
| tmp      | 1234   |
+----------+--------+
6 rows in set (0.00 sec)

mysql> delete from user where pass = 1234;
Query OK, 4 rows affected (0.15 sec)

mysql> select * from user;
+----------+--------+
| username | pass   |
+----------+--------+
| dawd     | 213123 |
| dokpd    | 213123 |
+----------+--------+
2 rows in set (0.00 sec)

8. rename table oldname to new name修改表的名字

正则表达式

mysql> select * from user where username regexp "^l";
+----------+--------+
| username | pass   |
+----------+--------+
| liming   | 345678 |
| ly       | 123456 |
+----------+--------+
2 rows in set (0.00 sec)

mysql> delete from user where username regexp "^l";
Query OK, 2 rows affected (0.37 sec)

mysql> select * from user;
+----------+--------+
| username | pass   |
+----------+--------+
| admin    | 888888 |
+----------+--------+
1 row in set (0.00 sec)