什么是SQL

SQL 结构化查询语言

SQL编程语言标准 对所有关系型数据库

数据库的基本sql语法

库

1. 创建库
2. 查看所有库
3. 查看库的详细信息
4. 删除库

表

1. 创建表
2. 查看所有的表（所在库下）
3. 查看表结构

desc  tb_name;

4. 删除表 （drop）

5. 插入数据
   注意： 在插入数据的时候一定要注意数据类型

语法： insert into  tb_name(k)Vlues(v)

root@localhost [y36]>insert  into  class (id)values(1);
Query OK, 1 row affected (0.06 sec)



root@localhost [y36]>insert into class (id,name,age)values(2,'ZhangSan',18);
Query OK, 1 row affected (0.01 sec)

root@localhost [mysql]>insert into  y36.class(id,name,age)values(2,'XiaoLi',20);
Query OK, 1 row affected (0.01 sec)

6. 查询表里的数据

语法： select [*| 字段的名称 ] from [db]tb_name;
root@localhost [y36]>select * from class;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | NULL     | NULL |
| NULL | NULL     |   18 |
| NULL | sleep    | NULL |
|    2 | ZhangSan |   18 |
+------+----------+------+
4 rows in set (0.00 sec)


root@localhost [mysql]>select  *  from y36.class;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | NULL     | NULL |
| NULL | NULL     |   18 |
| NULL | sleep    | NULL |
|    2 | ZhangSan |   18 |
+------+----------+------+
4 rows in set (0.00 sec)



根据条件查询

root@localhost [mysql]>select  *  from  y36.class where  name='ZS';
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | ZS   |    2 |
+------+------+------+
1 row in set (0.00 sec)

案例:从user表查找root用户的数据

root@localhost [mysql]>select * from user where User='root' \G;


root@localhost [mysql]>select User from user where User='root';
+------+
| User |
+------+
| root |
+------+
1 row in set (0.00 sec)

7. 删除表里的数据

语法： delete  [option]  from tb_name;


root@localhost [mysql]>delete  from y36.class where id=1;
Query OK, 1 row affected (0.01 sec)


root@localhost [mysql]>delete  from y36.class where  age=18;
Query OK, 2 rows affected (0.01 sec)

8. 修改表结构

需求:在已有表中增加一个字段（sex）

语法: alter  [option]  tb_name  add [字段的名称]

root@localhost [y36]>alter  table  class  add  sex char;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


root@localhost [y36]>desc class;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(50) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
| sex   | char(1)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)


修改sex字段的数据类型

root@localhost [y36]>alter  table class  modify  column  sex  char(5);
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@localhost [y36]>desc class;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(50) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
| sex   | char(5)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

root@localhost [y36]>update  class  set  sex='MW' where id=2;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

root@localhost [y36]>select  * from class;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | ZS   |    2 | W    |
|    2 | LS   |    3 | MW   |
|    2 | XL   |    4 | MW   |
|    2 | XZ   |    5 | MW   |
|    3 | XW   |    6 | NULL |
+------+------+------+------+
5 rows in set (0.00 sec)

9. 更新数据

语法： update   tb_name  set assignment_list  where {条件}

示例：
root@localhost [y36]>update  class  set  sex='W' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [y36]>select * from class;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | ZS   |    2 | W    |
|    2 | LS   |    3 | NULL |
|    2 | XL   |    4 | NULL |
|    2 | XZ   |    5 | NULL |
|    3 | XW   |    6 | NULL |
+------+------+------+------+
5 rows in set (0.00 sec)



root@localhost [y36]>update  class  set  sex='M' where id=2;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

root@localhost [y36]>select * from class;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
|    1 | ZS   |    2 | W    |
|    2 | LS   |    3 | M    |
|    2 | XL   |    4 | M    |
|    2 | XZ   |    5 | M    |
|    3 | XW   |    6 | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)