Mysql 数据库管理之 —— SQL 语句

文章目录

一、SQL 语言简介

  SQL 语句用于维护管理数据库,包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语言分类

  • DDL:数据定义语言,用于创建数据库对象,如库、表、索引等。
  • DML:数据操纵语言,用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据。
  • DQL:数据查询语言,用于从数据表中查找符合条件的数据记录。
  • DCL:数据控制语言,用于设置或者更改数据库用户或角色权限(数据控制语句,用于控制不同数据段直接的许可和访问级别的语句,这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如 COMMIT、ROLLBACK、GRANT、REVOKE …)

二、DDL 语句

数据定义语言,用于创建数据库对象,如库、表、索引等
常用命令:

create	#创建
drop	#删除
alter	#修改

1. 创建新的数据库

CREATE DATABASE <数据库名>;

Mysql 数据库管理之 —— SQL 语句

2. 创建新的表

  • create table 表名 (字段1 数据类型,字段2 数据类型,[…],parmary key (主键名));
  • 主键一般选择能代表唯一性的字段,不允许取空值(NULL),一个表只能有一个主键
CREATE TABLE web_demo (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));
# NOT NULL    	不允许为空值
# name char(10)	名称最长 10 位
# decimal(5,2)	整数部分最长五位,小数两位 
# DEFAULT ''  	密码默认值为空
# PRIMARY KEY 	主键一般选择没有重复并且不为空值的字段

Mysql 数据库管理之 —— SQL 语句

3. 删除表

drop table <数据库名>.<表名>;
#不 use 进入库中需加上数据库名

Mysql 数据库管理之 —— SQL 语句

4. 删除数据库

drop database <数据库名>;

Mysql 数据库管理之 —— SQL 语句

三、DML 语句

数据操纵语言,用于对表中的数据进行管理,插入、删除和修改数据库中的数据
常用命令:

insert	#插入
update	#修改,更新
delete	#删除

1. 在表中插入数据

格式:

INSERT INTO 表名(字段1,字段2,...) VALUES(字段1的值,字段2的值,...);
INSERT INTO 表名 VALUES(字段1的值,字段2的值,...);

示例:

insert into web_demo (id,name,score,passwd) values(1,'zhangsan',70,PASSWORD('123456'));
insert into web_demo values(2,'lisi',85.5,PASSWORD('123456'));

————————————————————————————————————
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| game               |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

mysql> use game;
Database changed
mysql> show tables;
+----------------+
| Tables_in_game |
+----------------+
| web_demo       |
+----------------+
1 row in set (0.00 sec)

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

mysql> insert into web_demo (id,name,score,passwd) values(1,'zhangsan',70,PASSWORD('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from web_demo;
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  1 | zhangsan | 70.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> desc web_demo;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | char(10)     | NO   |     | NULL    |       |
| score  | decimal(5,2) | YES  |     | NULL    |       |
| passwd | char(48)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into web_demo values(2,'lisi',85.5,PASSWORD('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from web_demo;
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  1 | zhangsan | 70.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  2 | lisi     | 85.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

PS:

select * from <tables>;		#查看表中的所有信息
desc <tables>;				#查看表结构属性,describe 的缩写
密码字串以加密形式显示,若不使用 PASSWORD(),查询时以明文显示

desc 显示字段含义:
————————————————
Field:字段名称
type:数据类型
Null:是否允许为空
Key:主键
Default:默认值
Extra:扩展属性(例如:标志符列(增量/步长)1 2)

2. 修改、更新数据表中的数据记录

格式:

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

示例:

update web_demo set passwd=PASSWORD('') where name='zhangsan';
update web_demo set name='lisi',passwd='' where id=2;

——————————————————————————————————————————————————————————————
mysql> update web_demo set passwd=PASSWORD('') where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update web_demo set name='lisi',passwd='' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from web_demo;
+----+----------+-------+--------+
| id | name     | score | passwd |
+----+----------+-------+--------+
|  1 | zhangsan | 70.00 |        |
|  2 | lisi     | 85.50 |        |
+----+----------+-------+--------+
2 rows in set (0.00 sec)

3. 在数据表中删除指定的数据记录

格式:

DELETE FROM 表名 [WHERE 条件表达式];

示例:

delete from web_demo where id=2;

————————————————————————————————————
mysql> select * from web_demo;
+----+----------+-------+--------+
| id | name     | score | passwd |
+----+----------+-------+--------+
|  1 | zhangsan | 70.00 |        |
|  2 | lisi     | 85.50 |        |
+----+----------+-------+--------+
2 rows in set (0.00 sec)

mysql> delete from web_demo where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from web_demo;
+----+----------+-------+--------+
| id | name     | score | passwd |
+----+----------+-------+--------+
|  1 | zhangsan | 70.00 |        |
+----+----------+-------+--------+
1 row in set (0.00 sec)

四、DQL 语句

用于从数据表中查找符合条件的数据记录
常用命令:

select

格式:

SELECT 字段名1,字段名2,[...] FROM 表名[WHERE 条件表达式];
——————————————————————————————————————————————————————
select * from web_demo;
select id,name,score from web_demo where id=2;
select name from web_demo\G  	 	 #以列表方式竖向显示
select * from web_demo limit 2;   	 #只显示头 2 行
select * from web_demo limit 2,3;    #显示第 2 行后的前 3 行

示例:

mysql> select * from web_demo;
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  1 | zhangsan | 70.00 |                                           |
|  2 | lisi     | 85.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  3 | wangwu   | 86.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  4 | xiaohong | 65.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  5 | xiaobai  | 70.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  6 | xiaolan  | 99.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> select id,name,score from web_demo where id=2;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | lisi | 85.50 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> select name from web_demo\G;
*************************** 1. row ***************************
name: zhangsan
*************************** 2. row ***************************
name: lisi
*************************** 3. row ***************************
name: wangwu
*************************** 4. row ***************************
name: xiaohong
*************************** 5. row ***************************
name: xiaobai
*************************** 6. row ***************************
name: xiaolan
6 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from web_demo limit 2;
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  1 | zhangsan | 70.00 |                                           |
|  2 | lisi     | 85.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from web_demo limit 2,3;
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  3 | wangwu   | 86.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  4 | xiaohong | 65.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  5 | xiaobai  | 70.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
3 rows in set (0.00 sec)

五、DCL 语句

上一篇:MySQL数据库授权与索引


下一篇:Day63~65(MySQL高级)