初识MySQL之——SQL语言基础
写在前面
在对数据库的日常管理和应用中，不论管理员合适要访问一个数据库，几乎都要使用到SQL语言，因此，熟练掌握SQL语言以及其脚本程序的编写是非常重要的。SQL（结构化查询语言）是目前最流行的关系型数据库查询语言，也是数据库的标准语言。
数据库语言分类
数据语言按照不同的功用可以分为四大类：数据库定义语言（DDL）、数据库操作语言（DML）、数据库控制语言（DCL）、数据库查询语言（DSL）。
DDL：数据库定义语言由一组SQL命令组成，用于创建和定义数据库对象。比如可以创建数据库，创建表，修改视图等。数据库对象有库、用户、视图、表、触发器、以及存储过程等。DDL语句以及其功能如下：
create alter drop rename truncate | 删除数据库对象 修改数据库对象 创建数据库对象 修改数据库对象名称 删除表的全部内容 |
DML：数据库操纵语言主要用来处理数据库中的数据内容，增删改等关于数据的内容变更的操作。
insert update delete call merge commit rollback | 插入数据到表或者视图 更新 删除 调用过程 合并操作 将当前更改的事物提交，写入数据库 回滚，取消上次提交以来的所有操作 |
DCL：数据库控制语言一般用来修改数据库的操作权限问题
grant revoke | 授予权限 回收权限 |
DSL：数据库查询语言，对数据库进行内容的查询。其语法在整个SQL语言中最复杂，最丰富。功能也非常强大。
select | 从表或者视图中检索数据 |
注意：对于MySQL等数据库管理软件来讲，它不区分大小写。但是为了养成良好的编程习惯还是要遵循一定的规则。建议SQL关键字大写或者一直保持一致的书写方式。因为SQL执行过的SQL关键字保存在缓存中，一致的书写方式更容易缓存命中，提高执行效率。
SQL语句简单实例
帮助使用方法
学习任何东西学会使用帮助用法是自我提高最有效的方式。mysql使用help keywords来查看帮助信息。
例如：
MariaDB [(none)]> help drop database 删库的正确姿势
Name: 'DROP DATABASE'
Description:
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
简单实例
[root@zachary ~]# mysql -u root -p MariaDB [(none)]> show databases; #显示所有数据库 +--------------------+ | Database | +--------------------+ | information_schema| | mysql | | performance_schema| | test | +--------------------+ 4 rows in set (0.00 sec) |
创建数据库
MariaDB [(none)]> create database if not exists zachary; 如果数据不存在就创建 Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use Zachary 把当前使用的数据库切换为Zachary Database changed |
创建表
MariaDB [zachary]> create table person( -> id tinyint primary key, -> name varchar(8) not null, -> age tinyint); Query OK, 0 rows affected (0.02 sec) MariaDB [zachary]> show tables; +-------------------+ | Tables_in_zachary | +-------------------+ | person | +-------------------+ 1 row in set (0.01 sec) |
修改表
MariaDB [zachary]> desc person; #查看表结构 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | tinyint(4) | NO | PRI | NULL | | | name | varchar(8) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [zachary]> alter table person modify id int; 修改id字段的数据类型 MariaDB [zachary]> alter table person add email varchar(20);新增列email MariaDB [zachary]> desc person; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(8) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | | email | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) MariaDB [zachary]> alter table person drop email ; #删除列mysql不支持多列删除 MariaDB [zachary]> alter table person change age sex varchar(2);修改列名 MariaDB [zachary]> alter table person rename to student; 重命名表 MariaDB [zachary]> rename table person to student; 重命名表 |
修改约束条件
MariaDB [zachary]> alter table student add unique key(name);添加唯一键 MariaDB [zachary]> alter table student add check(sex in('m','f'));添加check约束 MariaDB [zachary]> desc student; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default| Extra | +-------+------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(8)| NO | UNI | NULL | | | sex | varchar(2 | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
删除表
MariaDB [zachary]> drop table student; |
插入数据
MariaDB [(zachary)]> create table if not exists test( -> id tinyint unsigned primary key, -> name varchar(20) not null, -> sex enum('m','f') default 'm', 默认值 插入时若为指定值默认为'm' -> email varchar(30)); MariaDB [zachary]> insert into test values(1,'zachary','m','zachary_yzh@126.com');按照创建数据库字段的顺序插入数据 MariaDB [zachary]> insert into test (id,sex,name,email) values(2,'f','marry','marry@163.com');按照自定义的字段顺序插入数据 MariaDB [zachary]> insert into test (id,sex,name,email) values(3,'m','jack','jack@qq.com'),(4,null,'mali','mali@foxmail.com'); 一次插入多行数据 |
查询和修改表中数据
MariaDB [zachary]> select * from test; 查询表中所有数据 +----+---------+------+---------------------+ | id | name | sex | email | +----+---------+------+---------------------+ | 1 | zachary | m | zachary_yzh@126.com | | 2 | marry | f | marry@163.com | | 3 | jack | m | jack@qq.com | | 4 | mali | NULL | mali@foxmail.com | +----+---------+------+---------------------+ MariaDB [zachary]> update test set sex='f' where id=4; 修改mali的性别 MariaDB [zachary]> select name from test where sex='m';查看表中的男性都有谁。 MariaDB [zachary]> select name from test where id >=3; id大于3的人 MariaDB [zachary]> select name from test where id >=2 and id <=4;使用逻辑运算确定查询条件。表示id在2到4之间的人。逻辑运算符有and or not MariaDB [zachary]> select name from test where id between 2 and 4;使用between and关键字来确定区间范围。between and为闭区间 MariaDB [zachary]> insert into test (id,name)value(5,'tom');插入tom的信息。 MariaDB [zachary]> select * from test where name='tom'; 查看tom的信息。插入时没有插入性别。使用在创建表时所使用的默认值。 +----+------+------+-------+ | id | name | sex | email | +----+------+------+-------+ | 5 | tom | m | NULL | +----+------+------+-------+ 1 row in set (0.01 sec) MariaDB [zachary]> select name from test where email rlike '.*[(163)|(126)].*'; 查看使用网易邮箱的人有哪些，rlike为正则表达式匹配方式 MariaDB [zachary]> select * from test where name like '__r%';通配符匹配查询方式使用like关键字，查询名字中第三个字母为r的用户信息。_表示任意单个字符，%表示任意长度的任意字符。 MariaDB [zachary]> select * from test where email is null;查询没有使用邮箱的用户。在查询关键字为NULL时不能使用==号来进行匹配，要使用is null 或者is not null。 |
使用select语句创建表中数据
MariaDB [zachary]> create table emp select * from test; MariaDB [zachary]> select * from emp; +----+---------+------+---------------------+ | id | name | sex | email | +----+---------+------+---------------------+ | 1 | zachary | m | zachary_yzh@126.com | | 2 | marry | f | marry@163.com | | 3 | jack | m | jack@qq.com | | 4 | mali | f | mali@foxmail.com | | 5 | tom | m | NULL | +----+---------+------+---------------------+ 5 rows in set (0.00 sec) |
删除表中数据
MariaDB [zachary]> delete from emp where email is null;删除email为空的用户数据 MariaDB [zachary]> truncate emp; 清空表中所有数据（不记录日志） |
创建用户及授权
MariaDB [zachary]> create user 'yzh'@'172.18.%.%' identified by 'yzh01'; 创建用户及修改密码 在mysql中用户账号由用户名和用户主机名组成。主机可以使用网段，通配符或者主机名，使用主机时要能与解析的ip相对应。 MariaDB [zachary]> set password for 'yzh'@'172.18.%.%'=password('123456'); 修改用户口令的正确姿势,不建议直接更改mysql.user表 MariaDB [zachary]> grant select ,insert on Zachary.test to 'yzh'@'172.18.%.%'; 授予用户yzh对test表的查询和插入权限。 MariaDB [zachary]> grant ALL on zachary.* to 'tony'@'localhost' identified by 'zacharyadmin' with grant option;;在授予权限的时候直接创建用户。授予tony用户在Zachary数据库上的所有权限，并且该用户可以给其他用户授予权限，with grant option选项可以在授予其他用户自己所拥有的权限，慎用。 MariaDB [zachary]> revoke insert on zachary.test from 'yzh'@'172.18.%.%'; 回收yzh用户在Zachary.test表的插入权限 MariaDB [(none)]> create user 'test'@'localhost' identified by '123456';创建一个test用户 [root@zachary ~]# mysql -u tony -p 使用tony用户连接 Enter password: MariaDB [zachary]> select user(); +----------------+ | user() | +----------------+ | tony@localhost | +----------------+ 1 row in set (0.00 sec) MariaDB [zachary]> grant select on zachary.* to 'test'@'localhost'; 使用tony用户授予test用户对Zachary数据库的查询操作。 [root@zachary ~]# mysql -u root -p MariaDB [(none)]> revoke all on zachary.* from 'tony'@'localhost';回收tony用户的所有权限，但是他授权的其他用户的权限不受影响。Revoke的权限不会级联回收 [root@zachary ~]# mysql -u test -p #使用test用户仍旧能够查询 MariaDB [(none)]> select * from zachary.test; +----+---------+------+---------------------+ | id | name | sex | email | +----+---------+------+---------------------+ | 1 | zachary | m | zachary_yzh@126.com | | 2 | marry | f | marry@163.com | | 3 | jack | m | jack@qq.com | .. .. |
