SQL概述
SQL语言简介
1、Structured Query Language的缩写，即结构化查询语言
2、关系型数据库的标准语言
3、用于维护管理数据库
包括数据查询、数据更新、访问控制、对象管理等功能
SQL分类
DDL：（Data Definition Language，数据定义语言）：用来建立数据库、数据库对象和定义字段，如CERATE、ALTER、DROP。
DML：（Data Manipulation Language，数据操纵语言）：用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE。
DQL：（Data Query Language，数据查询语言）：用来查询数据库中的数据，如SELECT。
DCL：（Data Control Language，数据控制语言）：用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE。
创建数据库和表
DDL语句可用于创建数据库对象，如库、表、索引等
格式
create table 表名（字段01名称 字段01类型 字段01约束，字段02名称 字段02类型 字段02约束,…）存储引擎，字符集
字段名称、类型、约束解释：
字段01名称：属性名称，自定义
字段01类型：int（4） 整型 代表0000-9999
double 浮点型
decimal（5,2）有效数字是5位，小数点后面保留2位 100.00；099.50
float 单精度浮点 4字节
char 字符
char （10）固定长度字符串，字符串要用单引号引起来
varchar（50）可变长度字符串
字段01约束：
非空约束：内容不允许为空 not null
主键约束：非空且唯一 标识 primary key（主键）
默认约束：假如没有填数据，默认预先设定的值填写 default ‘未知’
自增特性：id 1 2 3 4 auto_increment（自动增长）
存储引擎：myisam innodb
字符集：UTF-8
mysql> create database lcx; #新建一个名为“lcx”库
Query OK, 1 row affected (0.00 sec)
mysql> use lcx; #进入“lcx”库
Database changed
mysql> create table info (id char(16) not null, passwd char(32) default'', primary key(id)); #创建一个表名为info的表，字符串一为字符串长度为10，内容不允许为空的非空约束；字符串二为passwd，字符串为长度为32，值为默认default，非空其唯一标识primary key的主键约束
Query OK, 0 rows affected (0.01 sec)
mysql> describe lcx.info; #查看lcx库info表
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id | char(16) | NO | PRI | NULL | |
| passwd | char(32) | YES | | | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
删除数据库和表
删除表
mysql> drop table lcx.info; #drop删除
Query OK, 0 rows affected (0.00 sec)
mysql> show tables; #查看表
Empty set (0.00 sec)
删除库
mysql> drop database lcx; #删除库lcx
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
查看数据库结构
查看已存在的数据库信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql默认4个数据库
**information_schema：**定义访问数据库元数据的方式。数据库名和表名，列的数据类型、访问权限等。
**mysql：**核心数据库，负责存储数据库用户、权限、关键字等用户自己需要使用的控制和管理信息。
**performance_schema：**数据库的性能参数，存储引擎等。
sys：sys系统库下包含许多视图，它们以各种方式对preformance_schema表进行聚合计算展示。
查看数据库表信息
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
显示数据表的结构（字段）
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
管理数据表中的数据
DML语句用于对表中的数据进行管理
包括的操作
- insert：插入新数据
- update：更新原有数据
- delete：删除不需要的数据
对数据的操作用DELETE，对库和表用drop
向数据表中插入新的数据内容
完整格式：insert into info (字段1，字段2) values (字段1的值，字段2的值)
加字段
mysql> create table info (id char(16) not null, name varchar(32) not null, passwd char(32) default'', primary key(id)); #创建一个info表，字符串一为id，长度为16，不可为空；字符串二为name，可变长度为32，不可为空；字符串三为passwd，长度为32，默认为default；非空且唯一标识peimary key
Query OK, 0 rows affected (0.01 sec)
mysql> insert into info(id,name,passwd)values(1,'zhangsan',123456);增加id为1，name为zhangsan，passwd为123456的字段
Query OK, 1 row affected (0.00 sec)
mysql> select * from lcx.info; #查看添加的字段，select对数据操作，show对库和表操作
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 123456 |
+----+----------+--------+
1 row in set (0.00 sec)
不加字段，默认为所有字段，每个字段都要写
mysql> insert into info values(2,'wangwu',123456);
Query OK, 1 row affected (0.00 sec)
mysql> select * from lcx.info;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 123456 |
| 2 | wangwu | 123456 |
+----+----------+--------+
2 rows in set (0.00 sec)
修改更新数据记录
格式：update 表名 set 字段名1=值1,字段名2=值2 where 条件表达式
mysql> update info set passwd=123 where name='zhangsan'; #修改zhangsan的passwd为123
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from lcx.info;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 123 |
| 2 | wangwu | 123456 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> update mysql.user set authentication_string=password('123456') where user='root'; #修改用户密码为123456
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
[root@server1 ~]# systemctl restart mysqld.service #重启数据库后生效
在数据库中删除指定的数据记录
格式：delete from 表名 where 条件表达式（不带where代表删除表中所有记录）
mysql> select * from lcx.info;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 123 |
| 2 | wangwu | 123456 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> delete from info where name='wangwu'; #删除info表中wangwu的数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from lcx.info;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 123 |
+----+----------+--------+
1 row in set (0.00 sec)
数据库的高级操作
清空表
delete from info；
truncate table info；
truncate清空表，表还在；drop是删除表中所有记录。
truncate和delete是两者的新值初始id不同。
临时表
临时建立的表，用于保存一些临时数据，不会长期存在
innodb 支持事务；写在括号外面的是对整张表的设定。
show tables；看不到临时表
mysql> create temporary table info (id char(16) not null, name varchar(32) not null, passwd char(32) default'', primary key(id))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
克隆表
like方法：从info表完整复制结构生成test1表，再导入数据
mysql> create table test1 like info;
mysql> insert into test1 select * from info;
show create table方法
新建一个test2表，读取test1表中的所有内容放入test2表
mysql> create table test2 as select * from test1;
数据库用户授权
grant：当用户已存在时，直接提权。
当用户不存在时，先创建用户，再提权。
revoke：只撤销权限，不删除用户
DCL语句设置用户权限（用户不存在时，则新建用户）
#设置登录密码为abc的zhangsan用户，可以从任意终端登录，对所有库和所有表有全部权限
mysql> grant all on *.* to 'zhangsan'@'%' identified by '123456';
#授权密码为123456的zhangsan用户，可以从任意终端登录，对所有库和所有表有全部权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| zhangsan | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
#设置登录密码为abc123的tom用户，可以从本地终端登录，对mysql库中的user表有select权限
grant select on *.* to 'zhangsan'@'locathost' identified by 'abc'; #授权zhangsan用户对所有库和所有表select权限
查看用户的权限
查看当前用户的权限（zhangsan用户下）
mysql> show grants;
+----------------------------------------------------------+
| Grants for zhangsan@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
| GRANT SELECT ON "mysql"."user" TO 'zhangsan'@'localhost' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'zhangsan'@'localhost'; #查看从本地登录的zhangsan用户的权限（root用户下）
+----------------------------------------------------------+
| Grants for zhangsan@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
| GRANT SELECT ON "mysql"."user" TO 'zhangsan'@'localhost' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
查看当前系统中的用户（root用户下）
mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| mysql.session |
| mysql.sys |
| root |
| zhangsan |
+---------------+
5 rows in set (0.00 sec)
扫一扫
2516
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
