SQL语句概述
SQL语言
- Structured Query Language的缩写,即结构化查询语言
- 关系型数据库的标准语言
- 用于维护管理数据库
·包括数据查询、数据更新、访问控制、对象管理等功能
SQL分类
- DDL:数据定义语言
- DML:数据操纵语言
- DQL:数据查询语言
- DCL:数据控制语言
数据的分类
结构化数据和非结构化数据
- 可以用二维逻辑表来表现的数据即为结构化数据,不方便用二维逻辑表表现的数据为非结构化数据。
数据库的分类
关系型数据库和非关系型数据库
- 关系型数据库里存储的数据是表格式的,因此存储在数据表的行和列中,数据表可彼此关联协作存储,容易数据提取。
- 非关系型数据库里数据不适合以表格形式存取,而是大块结合在一起,通常存储在数据集中,方便存储文档、图片等数据。
mysql数据库常用语句
分类
- DDL (Data Definition Language,数据定义语言):用来建立数据库、数据库对象和定义字段,如CREATE、ALTER、DROP。
- DML (Data Manipulation Language,数据操纵语言):用来插入、删除和修改数据库中的数据,如
INSERT、UPDATE、DELETE。 - DQL (Data Query Language,数据查询语言):用来查询数据库中的数据,如SELECT。
- DCL(Data Control Language,数据控制语言):用来控制数据库组件的存取许可、存取权限等,如COMMIT、ROLLBACK、GRANT、REVOKE。
DDL
创建新的数据库
mysql> create database auth;
Query OK, 1 row affected (0.01 sec)
创建新的表
- CREATE TABLE表名(字段1名称类型,字段⒉名称类型,…,PRIMARY KEY(主键名))
mysql> use auth;
Database changed
mysql> create table users(user_name char(64) not null primary key,user_passwd varchar(64) default'');
Query OK, 0 rows affected (0.02 sec)
注:定义字段属性后需用空格隔开
描述一个数据表
mysql> desc users;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| user_name | char(64) | NO | PRI | NULL | |
| user_passwd | varchar(64) | YES | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
增加一个数字段
mysql> create table cj(id int(10) auto_increment primary key,age int(3) not null,name varchar(128) not null,score decimal(5));
Query OK, 0 rows affected (0.01 sec)
增加/删除一个数据表
mysql> create table cj2 like cj;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table cj2;
Query OK, 0 rows affected (0.00 sec)
增加/删除一个数据库
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> drop database abc;
Query OK, 0 rows affected (0.01 sec)
DML
插入数据记录
- INSERT INTO 表名(字段1,字段2,…) VALUES(字段1的值,字段2的值,…)
mysql> insert into users(user_name,user_passwd) values('zhangsan','123123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into users values('lisi',PASSWORD('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)
修改数据记录
- UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2〕WHERE 条件表达式.
mysql> update cj set age=18 where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update cj set age=18;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 5 Changed: 2 Warnings: 0
修改数据库密码
mysql> update mysql.user set authentication_string=password('123456') where user='root';
[root@www ~]# mysqladmin -u root -p'123457’password '123456'
删除数据库记录
- DELETE FROM 表名 WHERE 条件表达式
mysql>DELETE FROM auth.users WHERE user_name='lisi';
mysql>SELECT*FROM auth.users;
DQL
查询数据记录
- SELECT 字段名1,字段名2,…FROM 表名WHERE 条件表达式
mysql> select * from cj;
+----+-----+----------+-------+------+
| id | age | name | score | addr |
+----+-----+----------+-------+------+
| 6 | 17 | zhangsan | 60 | 101 |
| 7 | 18 | lisi | 70 | 102 |
| 8 | 18 | wangwu | 80 | 103 |
| 9 | 17 | zhouliu | 95 | 104 |
| 10 | 19 | tianqi | 55 | 105 |
+----+-----+----------+-------+------+
5 rows in set (0.00 sec)
mysql> select name,age from cj;
+----------+-----+
| name | age |
+----------+-----+
| zhangsan | 17 |
| lisi | 18 |
| wangwu | 18 |
| zhouliu | 17 |
| tianqi | 19 |
+----------+-----+
5 rows in set (0.00 sec)
DCL
授予权限
- GRANT 权限列表 ON 数据库名. 表名 TO 用户名@来源地址 [ IDENTIFIED BY ‘密码’ ]
mysql> grant select on aaa.cj to 'zhangsan'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
附
mysql>grant all on bdqn.* to 'dbuser'@'20.0.0.11' identified by 'pwd@0123'; //适用5.6版本
查看权限
- SHOW GRANTS FOR 用户名@来源地址
mysql> grant select on aaa.cj to 'zhangsan'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for 'zhangsan'@'localhost';
+------------------------------------------------------+
| Grants for zhangsan@localhost |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
| GRANT SELECT ON "aaa"."cj" TO 'zhangsan'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> exit
[root@server1 ~]# mysql -ulisi -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from aaa.cj;
+----+-----+----------+-------+------+
| id | age | name | score | addr |
+----+-----+----------+-------+------+
| 6 | 17 | zhangsan | 60 | 101 |
| 7 | 18 | lisi | 70 | 102 |
| 8 | 18 | wangwu | 80 | 103 |
| 9 | 17 | zhouliu | 95 | 104 |
| 10 | 19 | tianqi | 55 | 105 |
+----+-----+----------+-------+------+
5 rows in set (0.00 sec)
撤销权限
- REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
[root@server1 ~]# mysql -uroot -p 123123
Enter password:
ERROR 1049 (42000): Unknown database '123123'
[root@server1 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> revoke select on *.* from 'lisi'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'lisi'@'localhost';
+------------------------------------------+
| Grants for lisi@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'localhost' |
+------------------------------------------+
1 row in set (0.01 sec)
附加
查看数据库信息
mysql>SHOW DATABASES;
查看数据库中的表信息
- USE 数据库名
- SHOW TABLES
临时表
- 临时建立的表,用于保存一些临时数据,不会长期存在且无法查看到
mysql> create temporary table ttt (id int(5) not null,name varchar(64) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| cj |
+---------------+
1 row in set (0.00 sec)
mysql> insert into ttt values(1,'lh'),(2,'mh');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from ttt;
+----+------+
| id | name |
+----+------+
| 1 | lh |
| 2 | mh |
+----+------+
2 rows in set (0.00 sec)
克隆表
- LIKE方法
mysql> create table cj2 like cj;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| cj |
| cj2 |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from cj2;
Empty set (0.00 sec)
mysql> desc cj2;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| age | int(3) | NO | | NULL | |
| name | varchar(128) | NO | | NULL | |
| score | decimal(5,0) | YES | | NULL | |
| addr | int(5) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
将cj内容复制到cj2中
mysql> insert into cj2 select * from cj;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from cj2;
+----+-----+----------+-------+------+
| id | age | name | score | addr |
+----+-----+----------+-------+------+
| 6 | 17 | zhangsan | 60 | 101 |
| 7 | 18 | lisi | 70 | 102 |
| 8 | 18 | wangwu | 80 | 103 |
| 9 | 17 | zhouliu | 95 | 104 |
| 10 | 19 | tianqi | 55 | 105 |
+----+-----+----------+-------+------+
5 rows in set (0.00 sec)
- SHOW CREATE TABLE方法
mysql> show create table cj;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cj | CREATE TABLE "cj" (
"id" int(10) NOT NULL AUTO_INCREMENT,
"age" int(3) NOT NULL,
"name" varchar(128) NOT NULL,
"score" decimal(5,0) DEFAULT NULL,
"addr" int(5) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
最后加\G,显示更加简介
mysql> show create table cj\G;
*************************** 1. row ***************************
Table: cj
Create Table: CREATE TABLE "cj" (
"id" int(10) NOT NULL AUTO_INCREMENT,
"age" int(3) NOT NULL,
"name" varchar(128) NOT NULL,
"score" decimal(5,0) DEFAULT NULL,
"addr" int(5) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
复制CREATE后的内容,并修改表名为t1,即可创建一个t1表与cj相同!
mysql> CREATE TABLE "t1" (
-> "id" int(10) NOT NULL AUTO_INCREMENT,
-> "age" int(3) NOT NULL,
-> "name" varchar(128) NOT NULL,
-> "score" decimal(5,0) DEFAULT NULL,
-> "addr" int(5) DEFAULT NULL,
-> PRIMARY KEY ("id")
-> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
内容与cj相同
char与varchar区别
char的长度是不可变的,而varchar的长度是可变的
- 字段b:类型char(10),值为: abc,存储为: abc (abc+7个空格)
- 字段d:类型varchar(10),值为: abc,存储为: abc(自动变为3个的长度)
超出长度自动截取
- 字段c:类型char(3), 值为: abcdefg,存储为: abc (defg自动删除)
- 字段e:类型varchar(3),值为: abcdefg,存储为: abc (defg自动删除)
var(10)和char(10),都表示可存10个字符,无论存放的是数字、字母还是UTF8汉字(每个汉字3字节),都可以存放10个
char最多可以存放255个字符
- varchar的最大长度为65535个字节,varchar可存放的字符数跟编码有关
- 字符类型若为gbk,每个字符最多占2个字节,最大长度不能超过32766个字符
- 字符类型若为utf8,每个字符最多占3个字节,最大长度不能超过21845个字符
注:
- 一个汉字占多少长度与编码有关;
- UTF-8:一个汉字=3个字节
- GBK:一个汉字=2个字节