CentOS MySQL语句入门

网友投稿 310 2022-08-22

CentOS MySQL语句入门

MySQL语句入门

MySQL的连接管理

自带的连接工具（客户端）

mysql

-u:user指定MySQL的用户 -p:password指定MySQL用户的密码 -S:socket指定socket文件的位置 -h:host指定主机IP地址 -e:exec执行SQL语句 -p:port指定端口 --protocol=name:指定连接方式

mysqladmin mysqldump（逻辑备份）

第三方连接工具（客户端）

Navicat SQLmanager SQLyog

MySQL启动关闭流程

启动

/etc/init.d/mysqld start systemctl start mysqld mysqld_safe --选项

关闭

/etc/init.d/mysqld stop systemctl stop mysqld mysqladmin -uroot -S /data/3309/data/3309.sock shutdown ------------------------------------------------------ kill -9 pid ? killall mysqld ? pkill mysqld ? ## 不到万不得已不要使用上面三个命令，非要用的话建议使用pkill mysqld

MySQL实例初始化配置

预编译命令行配置文件（读取顺序） /etc/my.cnf /etc/mysql/my.cnf $basedir/my.cnf --defaults-extra-file=/opt/my.cnf ~/.my.cnf

优先级

~/.my.cnf > --defaults-extra-file=/opt/my.cnf > $basedir/my.cnf > /etc/mysql/my.cnf >

/etc/my.cnf

注意：如果启动MySQL加了--defaults-file=/etc/my.cnf选项，其它位置的配置文件都不读取

思考

#cmake： socket=/application/mysql/tmp/mysql.sock #命令行： --socket=/tmp/mysql.sock #配置文件： /etc/my.cnf中[mysqld]标签下：socket=/opt/mysql.sock #default参数： --defaults-file=/tmp/a.txt配置文件中[mysqld]标签下：socket=/tmp/test.sock /application/mysql/tmp/mysql.sock /tmp/mysql.sock /opt/mysql.sock /tmp/test.sock mysqld --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock /tmp/mysql.sock ## 结论默认配置，优先级 1.命令行 2.配置文件 - ~/.my.cnf - --defaults-extra-file=/opt/my.cnf - $basedir/my.cnf - /etc/mysql/my.cnf - /etc/my.cnf 3.编译参数

初始化配置的作用

影响服务端的启动影响到客户端的连接

[mysqld] [server] ## 这两个标签下的配置，都是来影响服务端启动的 [mysql][mysqladmin][mysqldump] ## 这几个标签，影响对应的客户端命令 [client] ## 这个标签，影响所有的客户端命令 ## 注意：修改客户端配置，不需要重启mysql，修改服务端配置[mysqld]需要重启mysql [mysqld] skip_name_resolve basedir=/application/mysql datadir=/application/mysql/data server_id=10 socket=/opt/mysql.sock [client] user=root password=abc socket=/opt/mysql.sock

MySQL的SQL语句

客户端管理

mysql

## 查看命令帮助 ? \? help \h ## 查看状态 status \s ## 退出 exit quit \q ## 结束当前的SQL语句 \c ## Ctrl + c MySQL5.6中：退出MySQL MySQL5.7中：结束当前SQL语句，类似于\c ## 在MySQL中执行系统命令 system \! ## 临时将操作记录到指定的文件中 tee \T tee /tmp/mysql.log \T /tmp/mysql.log ## 切换数据库 use \u use mysql \u mysql ## 导入数据 source \. source /tmp/zls.sql \. /tmp/zls.sql ## 格式化（key:value）方式，显示数据 \G mysql> select * from mysql.user\G ### 客户端配置，显示当前所在数据库及登录用户 [client] prompt="\u@\h:\d>"

mysqladmin

mysqladmin password '密码' # 修改密码或者设置密码 mysqladmin shutdown # 关闭mysql服务 mysqladmin ping # 检测MySQL是否存活 mysqladmin status # 查看MySQL的状态 mysqladmin variables # 查看MySQL的默认配置（内置变量） mysqladmin create 库名 # 在库外创建数据库 mysqladmin drop 库名 # 在库外删除数据库 mysqladmin reload # 重新加载数据库 mysqladmin flush-log # 刷新授权表 ## 注意有密码:-u -p

SQL层的SQL语句

什么是SQL语句

结构化的查询语句标准：SQL-92

SQL语句的分类

DDL

Database Definition Language 数据定义语言 # 开发规范： (01) 表名不能大写,数字开头,16个字符串 (02) 表名和业务有关 (03) drop 语句禁止 (04) 选择合适的数据类型 (05) 必须要有主键 (06) 列尽量非空约束 (07) 减少外键约束 (08) 必须设置存储引擎和字符集 (09) 列必须要有注释 (10) 对于非负数设置数据类型约束--无符号

库

# 增语法： Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name } create database 库名; create schema 库名; create database 库名 character set utf8; create database 库名 charset utf8; create database if not exists 库名 charset utf8 collate utf8_bin; # 删 drop database 库名; # 改修改字符集 alter database charset latin1; #### DQL查看数据库的字符集 show create database www; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | | CREATE DATABASE `/*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+

表

# 增 create table 表名(字段1 数据类型约束,字段数据类型约束....) 建表，至少要给的是字段名称和数据类型 create table student(id int not null primary key auto_increment,name varchar(10),ageage tinyint,gender enum('0','1')); ### 简洁方式 mysql> create table student2( -> id int, -> name varchar(10), -> age tinyint, -> gender enum('0','1')); Query OK, 0 rows affected (0.02 sec) mysql> desc student2; +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | gender | enum('0','1') | YES | | NULL | | +--------+---------------+------+-----+---------+-------+ ## 数据类型 int：整数 -2^31 ~ 2^31 -1 varchar：字符类型（变长） char：字符类型（定长） smallint: 较小整数数据类型（-2^15到2^15-1） mediumint: 中型整数数据类型 bigint: 较大整数数据类型（-2^63到2^63-1） float: 浮点数（小型单精度四个字节浮点数） double:常规双精度（八个字节浮点数） decimal:定点数（包含整数部分，小鼠部分或同事包括两者的精确值数值） bit:位字段值 tinyint enum datetime timestamp # 最多输入十个字符 name varchar(10) # 占据十个字符 name char(10) # 只能输入11位的字符 phone char(11) tinyint：整数 -128 ~ 127 enum：枚举类型 enum('A','B','C','D') datetime：时间类型年月日时分秒 ## 约束 not null：非空 primary key：主键（唯一且非空的）一张表只能有一个主键 auto_increment：自增（此列必须是：primary key或者unique key） unique key：唯一键，单独的唯一的唯一键 + not null default：默认值 unsigned：无符号（非负） comment：注释 # 删 mysql> drop table student2; ## 删除用户 mysql> drop user wsh1@'localhost'; # 改 ## 修改表名 mysql> show tables; +---------------+ | Tables_in_| +---------------+ | student3 | +---------------+ 1 row in set (0.00 sec) mysql> alter table student3 rename stu; Query OK, 0 rows affected (0.12 sec) mysql> show tables; +---------------+ | Tables_in_| +---------------+ | stu | +---------------+ 1 row in set (0.00 sec) ## 添加字段(将字段添加在表的最后一列) mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> alter table stu add done varchar(10) not null; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | | done | varchar(10) | NO | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> alter table stu add wsh char(5) default '3'; ## 带默认值添加字段 Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | | done | varchar(10) | NO | | NULL | | | wsh | char(5) | YES | | 3 | | +--------+---------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) ## 添加多个字段 mysql> alter table stu add int(4),add qq char(11) not null; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | | done | varchar(10) | NO | | NULL | | | wsh | char(5) | YES | | 3 | | | | int(4) | YES | | NULL | | | qq | char(11) | NO | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec) ## 按指定位置添加字段 mysql> alter table stu add tt char(2) after age； Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | tt | char(2) | YES | | NULL | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | | done | varchar(10) | NO | | NULL | | | wsh | char(5) | YES | | 3 | | | | int(4) | YES | | NULL | | | qq | char(11) | NO | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 11 rows in set (0.00 sec) ## 将字段添加在最前面一列 mysql> alter table stu add ee int(3) first; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | ee | int(3) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | tt | char(2) | YES | | NULL | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | | done | varchar(10) | NO | | NULL | | | wsh | char(5) | YES | | 3 | | | | int(4) | YES | | NULL | | | qq | char(11) | NO | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 12 rows in set (0.00 sec) ## 删除字段 mysql> alter table stu drop qq; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table stu drop www; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | ee | int(3) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | tt | char(2) | YES | | NULL | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | | done | varchar(10) | NO | | NULL | | | wsh | char(5) | YES | | 3 | | +--------+---------------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec) ## 修改数据类型和属性 mysql> alter table stu change ee er char(12); Query OK, 5 rows affected (0.06 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | er | char(12) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | tt | char(2) | YES | | NULL | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | | done | varchar(10) | NO | | NULL | | | wsh | char(5) | YES | | 3 | | +--------+---------------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec)

DML

Data Manipulation Language 数据操作语言所有的DML都是操作表内容的 ## 增 # 插入单条数据 mysql> desc stu; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | gender | enum('0','1') | NO | | 1 | | | phone | char(11) | YES | | NULL | | | bir | datetime | NO | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> insert into stu(name,age,gender,phone,bir) value('wsh',22,'1',133,19900805); Query OK, 1 row affected (0.00 sec) mysql> select * from stu; +----+----------+-----+--------+-------------+---------------------+ | id | name | age | gender | phone | bir | +----+----------+-----+--------+-------------+---------------------+ | 1 | zhangsan | 18 | 0 | NULL | 0000-00-00 00:00:00 | | 2 | lisi | 22 | 1 | NULL | 0000-00-00 00:00:00 | | 3 | zhaoliu | 38 | 0 | NULL | 0000-00-00 00:00:00 | | 4 | qianqi | 44 | 1 | NULL | 0000-00-00 00:00:00 | | 5 | zhuba | 55 | 1 | 15858170920 | 1995-09-09 00:00:00 | | 6 | wangwu | 33 | 1 | 133 | 1722-05-06 00:00:00 | | 7 | wsh | 22 | 1 | 133 | 1990-08-05 00:00:00 | +----+----------+-----+--------+-------------+---------------------+ 7 rows in set (0.00 sec) # 插入多条数据(默认字段可不加) mysql> insert into stu(name,age,gender,phone,bir) -> value('qiqi',33,'0',153,19960506), -> ('wuwu',25,'1',666,20000605); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from stu; +----+----------+-----+--------+-------------+---------------------+ | id | name | age | gender | phone | bir | +----+----------+-----+--------+-------------+---------------------+ | 1 | zhangsan | 18 | 0 | NULL | 0000-00-00 00:00:00 | | 2 | lisi | 22 | 1 | NULL | 0000-00-00 00:00:00 | | 3 | zhaoliu | 38 | 0 | NULL | 0000-00-00 00:00:00 | | 4 | qianqi | 44 | 1 | NULL | 0000-00-00 00:00:00 | | 5 | zhuba | 55 | 1 | 15858170920 | 1995-09-09 00:00:00 | | 6 | wangwu | 33 | 1 | 133 | 1722-05-06 00:00:00 | | 7 | wsh | 22 | 1 | 133 | 1990-08-05 00:00:00 | | 8 | wsh | 22 | 1 | 133 | 1990-08-05 00:00:00 | | 9 | qiqi | 33 | 0 | 153 | 1996-05-06 00:00:00 | | 10 | wuwu | 25 | 1 | 666 | 2000-06-05 00:00:00 | +----+----------+-----+--------+-------------+---------------------+ 10 rows in set (0.00 sec) ## 删(危险) 使用delete一定要加条件 mysql> delete from stu; (不要这么删除) # 加条件 mysql> delete from stu where id=10; Query OK, 1 row affected (0.02 sec) mysql> select * from stu; +----+----------+-----+--------+-------------+---------------------+ | id | name | age | gender | phone | bir | +----+----------+-----+--------+-------------+---------------------+ | 1 | zhangsan | 18 | 0 | NULL | 0000-00-00 00:00:00 | | 2 | lisi | 22 | 1 | NULL | 0000-00-00 00:00:00 | | 3 | zhaoliu | 38 | 0 | NULL | 0000-00-00 00:00:00 | | 4 | qianqi | 44 | 1 | NULL | 0000-00-00 00:00:00 | | 5 | zhuba | 55 | 1 | 15858170920 | 1995-09-09 00:00:00 | | 6 | wangwu | 33 | 1 | 133 | 1722-05-06 00:00:00 | | 7 | wsh | 22 | 1 | 133 | 1990-08-05 00:00:00 | | 8 | wsh | 22 | 1 | 133 | 1990-08-05 00:00:00 | | 9 | qiqi | 33 | 0 | 153 | 1996-05-06 00:00:00 | +----+----------+-----+--------+-------------+---------------------+ 9 rows in set (0.00 sec) # 想全部删除也要加条件 mysql> delete from stu where 1=1; Query OK, 9 rows affected (0.00 sec) mysql> select * from stu; Empty set (0.00 sec) ## 注意：一般在删除数据时，我们会根据唯一性的字段，进行删除 delete from mysql.user where user='wsh' and host='localhost'; ## 改使用update一定要加条件 mysql> select * from stu; +----+------+-----+--------+-------+---------------------+ | id | name | age | gender | phone | bir | +----+------+-----+--------+-------+---------------------+ | 11 | qiqi | 33 | 0 | 153 | 1996-05-06 00:00:00 | | 12 | wuwu | 25 | 1 | 666 | 2000-06-05 00:00:00 | +----+------+-----+--------+-------+---------------------+ 2 rows in set (0.00 sec) mysql> update stu set id=1 where name='qiqi'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from stu; +----+------+-----+--------+-------+---------------------+ | id | name | age | gender | phone | bir | +----+------+-----+--------+-------+---------------------+ | 1 | qiqi | 33 | 0 | 153 | 1996-05-06 00:00:00 | | 12 | wuwu | 25 | 1 | 666 | 2000-06-05 00:00:00 | +----+------+-----+--------+-------+---------------------+ 2 rows in set (0.00 sec) ## 使用update替代delete删除数据 1.给表中，加一个状态列 mysql> alter table stu add status enum('0','1') default '1'; 2.使用update删除数据 mysql> update stu set status='0' where name='qiqi'; 3.查询的时候，使用where条件查询 ysql> select * from stu where status='1'; +----+------+-----+--------+-------+---------------------+--------+ | id | name | age | gender | phone | bir | status | +----+------+-----+--------+-------+---------------------+--------+ | 12 | wuwu | 25 | 1 | 666 | 2000-06-05 00:00:00 | 1 | +----+------+-----+--------+-------+---------------------+--------+ 注意：update修改数据库用户的密码，是要flush privileges;修改其他数据，不需要。

DCL

Database Control Language 数据控制语言 ### 赋予权限 5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，则无法创建，5.6和5.7新版本可以直接创建用户 grant all on *.* to test@'%' identified by '123'; grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码'; grant all on *.* to test@'%' identified by '123' with grant option; grant all on *.* to zls4@'%' identified by '123' with max_queries_per_hour 3; max_queries_per_hour 3 ## 限制该用户一小时内，只能查询3次 grant all on *.* to zls6@'%' identified by '123' without max_queries_per_hour 3 max_updates_per_hour 1 ## 限制用户一小时，只能执行一次update max_connections_per_hour 1 ## 限制用户一小时内，只能连接一次数据库 max_user_connections 1 ## 限制用户，只能同时一个用户连接 grant option; grant all on *.* to zls6@'%' identified by '123' with max_queries_per_hour 999 max_updates_per_hour 999 max_connections_per_hour 999 max_user_connections 999; ### 回收权限 revoke revoke 权限 on 库.表 from 用户@'主机域'; revoke delete on *.* from dev@'%';

DQL

Database Query Language 数据查询语言 ## show show databases; # 查看数据库 show tables; # 查看表 show tables from zls; # 查看指定数据库下的表，不用切换数据库 show create database 库名; # 查看建库语句 show create table 表名; # 查看建表语句，也是为了查看注释 show create table 库名.表名; # 查看建表语句，也是为了查看注释（不用切换数据库） show grants for 用户名@'主机域'; # 查看用户授权语句，也是为了查看该用户的权限 show variables; # 查看所有的内置变量（默认配置） show variables like '%server'; # 模糊查询（过滤） show variables like '%server%'; # 模糊查询（过滤） show variables like 'server%'; # 模糊查询（过滤） show processlist; # 查看后台执行的sql语句 show full processlist; # 查看完整的后台执行的sql语句 show collation; # 查看所有校验规则 show charset; # 查看所有字符集以及该字符集的默认校验规则 ## desc desc 库.表 # 查看表结构查看执行计划 ## explain 查看执行计划（索引） ## select [root@db01 ~]# wget http://test.driverzeng.com/MySQL_File/world.sql mysql> source /root/world.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | linux50 | | mysql | | performance_schema | | test | | world | | | +--------------------+ mysql> use world Database changed mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) ## select 常用简单查询 mysql> select * from city; ## 查看该表中所有的字段的记录(所有数据) mysql> select id,name,countrycode from city; ## 查看指定字段的所有数据 ## select 行级查询(翻页功能) mysql> select id,name,countrycode from city limit 5; ## 翻页功能,从第6开始查到20 mysql> select id,name,countrycode from city limit 5,20; ## select 条件查询 mysql> select * from city where countrycode='CHN'; mysql> select * from city where countrycode='CHN' or countrycode='USA'; mysql> select * from city where countrycode='CHN' and district='heilongjiang'; ### 条件查询结合行级查询 select * from city where countrycode='CHN' and district='heilongjiang' limit 10; ## select 模糊查询 mysql> select * from city where countrycode like '%HN'; mysql> select * from city where countrycode like '%HN' or countrycode='US%'; ## select 排序查询 order by select * from city where countrycode='CHN' order by population; ## 顺序排序 mysql> select * from city where countrycode='CHN' order by population desc; ##倒叙排序 ## 不加条件,顺序排序 mysql> select * from city order by population; ## 不加条件倒序排序 mysql> select * from city order by population desc; ## 不重复查询 select distinct 字段名 from 数据表 select distinct Director from movies ## select 范围查询 mysql> select * from city where population > 10000000; >:大于 <:小于 >=:大于等于 <=:小于等于 <>:不等于 !=:不等于 # in: mysql> select * from city where countrycode in ('CHN','USA'); # or: mysql> select * from city where countrycode='CHN' or countrycode='USA'; # union all 联合查询 mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; ## select 分组查询 group by group by + 聚合函数聚合函数： max()：最大值 min()：最小值 sum()：求和 avg()：求平均值` count()：统计 1.遇到统计想函数 2.形容词前groupby 3.函数中央是名词 4.列名select后添加 # 统计世界上每个国家的总人口数遇到统计想函数：sum() 形容词前groupby：group by countrycode 函数中央是名词：sum(population) 列名select后添加：国家人口数 countycode,population mysql> select countrycode,sum(population) from city group by countrycode; # 统计中国各个省的人口数量(练习) 遇到统计想函数：sum() 形容词前groupby：group by district 函数中央是名词：sum(population) 名select后添加：省人口数 district,population mysql> select district,sum(population) from city where countrycode='CHN' group by district; # 统每个国家的城市数量(练习) 遇到统计想函数：count() 形容词前groupby：group by countrycode 函数中央是名词：count(name) 名select后添加：国家，城市数量 countrycode,count(name) mysql> select countrycode,count(name) from city group by countrycode; ## 设置别名 select countrycode as 国家,count(name) as 城市数量 from city group by countrycode;

select高级用法

传统连接

自连接（NATURAL JOIN）

natural join select tb1.field1,tb2.field,tb1.field2 from tb1 natural join tb2 where 需求条件注意：两张表，要有等价的条件列，并且列名（字段名）也要一样 city 国家代码：countrycode country 国家代码： code countrylanguage 国家代码： countrycode ## 说english的城市有哪些，他们的国家代码是什么？ select city.countrycode,city.name,countrylanguage.language from city natural join countrylanguage where countrylanguage.language='english'; ## 每个国家有几个城市说英语，他们的国家代码是什么？ select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言 from city natural join countrylanguage where countrylanguage.language='english' group by countrycode; ## 每个国家有几个城市说英语，他们的国家代码是什么？按城市数量排序 select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言 from city natural join countrylanguage where countrylanguage.language='english' group by countrycode order by 城市数量;

内连接（join on）

## 两表 select tb1.field1,tb2.field,tb1.field2 from tb1 join tb2 on 等价条件 where 需求条件 ### 多表 select tb1.field,tb2.field,tb3.field from tb1 join tb2 on 等价条件1 join tb3 on 等价条件2 where 需求条件注意：两张表，要有等价的条件列，大表在前，小表在后 ## 每个国家有几个城市说英语，他们的国家代码是什么？ select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言 from city join countrylanguage on city.countrycode=countrylanguage.countrycode where countrylanguage.language='english' group by city.countrycode; ## 世界上大于10000000人口数量的城市在哪个国家，说什么语言？ 1 jion 2 on A 表1 jion 表2 on 等价条件A join 表3 on 等价条件B **** 传统连接 **** select country.name,city.name,city.population,countrylanguage.language from country,city,countrylanguage where country.code=city.countrycode and country.code=countrylanguage.countrycode and city.population > 10000000; **** 内连接三表连查 **** select country.name,city.name,city.population,countrylanguage.language from country join city on country.code=city.countrycode join countrylanguage on country.code=countrylanguage.countrycode where city.population > 10000000;

外连接(了解即可)

## 左外连接 left join - 左外连接 left join select tb1.field1,tb2.field,tb1.field2 from tb1 left join tb2 on 等价条件 and 需求条件显示左边表的内容，tb1，右边表结果显示NULL - 右外连接 right join select tb1.field1,tb2.field,tb1.field2 from tb1 right join tb2 on 等价条件显示右边表的内容，tb2，左边表结果显示NULL select city.name,city.countrycode,country.name from city left join country on city.countrycode=country.code and city.population<100; ## 右外连接 right join select city.name,city.countrycode,country.name from city right join country on city.countrycode=country.code and city.population<100;

联合查询

#范围查询OR语句 mysql> select * from city where countrycode='CHN' or countrycode='USA'; #范围查询IN语句 mysql> select * from city where countrycode in ('CHN','USA'); # 替换为： mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; union：去重复合并 union all ：不去重复使用情况：union < union all

字符集设置

操作系统设置字符集

## CentOS6 [root@db01 ~]# source /etc/sysconfig/i18n [root@db01 ~]# echo $LANG zh_CN.UTF-8 ## CentOS7 [root@db01 ~]# echo $LANG en_US.UTF-8 # 如不是utf8 [root@db01 ~]# vim /etc/locale.conf LANG="en_US.UTF-8"

连接工具设置字符集

Xshell

## windows GB2312 国标 GBK 国标扩

建库建表级别设置字符集

## 建库设置字符集 mysql> create databae wsh charset utf8 collate utf8_general_ci; ## 建表设置字符集 create table test( id int, name varchar(10) ) default charset utf8;

数据库配置设置字符集

## 一般只有在二进制安装的mysql的时候默认为非utf8 [root@db02 ~]# vim /etc/my.cnf [mysqld] character_set_server=utf8

练习题

建库

库名：linux50 字符集：utf8 校验规则：utf8_general_ci

建表

表一

表名：student（学生表）

字段数据类型要求是否为空注释sno 最多20位否学号（主键）sname 可变长否学生姓名sage 最小整数，非负数否学生年龄ssex 0，1 否学生性别（1是男，0是女s）默认为男）sbirthday 时间类型默认为空学生生日class 可变长否学生班级

表二

表名：course（课程表）

字段数据类型要求是否为空注释cno 最多20位否课程号（主键）cname 可变长否课程名称tno 可变长否教师编号

表三

表名：score（成绩表）

字段数据类型要求是否为空注释sno 最多20位否学号（主键）cno 最多20位否课程号（主键）mark 浮点数(4,1) 否成绩

表四

表名：teacher（教师表）

字段数据类型要求是否为空注释tno 最多20位否教师编号（主键）tname 可变长否教师姓名tage 最小整数，非负数否教师年龄tsex 0，1 否教师性别（1是男，0是女）默认为男）prof 可变长是教师职称depart 可变长否教师部门

插入数据练习

1.将自己班级小组所有人员信息插入到student表中（数据自定义）

mysql> insert into student(sname,sage,ssex,sbirthday,class) value -> ('徐导',20,'1',now(),'1'), -> ('曾导',18,'1',now(),'1'), -> ('李导',25,'1',now(),'2'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student; +-----+--------+------+------+---------------------+-------+ | sno | sname | sage | ssex | sbirthday | class | +-----+--------+------+------+---------------------+-------+ | 1 | 徐导 | 20 | 1 | 2022-08-14 16:22:39 | 1 | | 2 | 曾导 | 18 | 1 | 2022-08-14 16:22:39 | 1 | | 3 | 李导 | 25 | 1 | 2022-08-14 16:22:39 | 2 | +-----+--------+------+------+---------------------+-------+ 3 rows in set (0.00 sec)

2.将曾志高翔、徐亮伟、李永义信息插入教师表中（数据自定义）

3.将数学、语文、英语学科插入到课程表中（数据自定义）

mysql> insert into course(cno,cname,tno) value(1,'英语','001'),(2,'语文','002'),(3,'数学','003'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from course; +-----+--------+-----+ | cno | cname | tno | +-----+--------+-----+ | 1 | 英语 | 001 | | 2 | 语文 | 002 | | 3 | 数学 | 003 | +-----+--------+-----+ 3 rows in set (0.00 sec)

4.将分数插入到成绩表中（数据自定义）

mysql> insert into score(sno,cno,mark) value(1,1,90.0), -> (2,1,10.0), -> (3,1,60.0), -> (1,2,90.0), -> (2,2,99.5), -> (3,2,80.0), -> (1,3,80.5), -> (2,3,60.0), -> (3,3,88.0); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from score; +-----+-----+------+ | sno | cno | mark | +-----+-----+------+ | 1 | 1 | 90.0 | | 2 | 1 | 10.0 | | 3 | 1 | 60.0 | | 1 | 2 | 90.0 | | 2 | 2 | 99.5 | | 3 | 2 | 80.0 | | 1 | 3 | 80.5 | | 2 | 3 | 60.0 | | 3 | 3 | 88.0 | +-----+-----+------+ 9 rows in set (0.00 sec)

查询练习

1.查询student表中的所有记录的sname、ssex和class列。

mysql> select sname,ssex,class from student; +--------+------+-------+ | sname | ssex | class | +--------+------+-------+ | 徐导 | 1 | 1 | | 曾导 | 1 | 1 | | 李导 | 1 | 2 | +--------+------+-------+ 3 rows in set (0.01 sec)

2.查询教师所有的单位即不重复的depart列。

mysql> insert into teacher(tname,tage,tsex,prof,depart) -> value('曾志高翔',18,'1','教学总监','语言系'), -> ('徐亮伟',50,'1','讲师','文学系'), -> ('李永义',80,'1','助教','科学系'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from teacher; +-----+--------------+------+------+--------------+-----------+ | tno | tname | tage | tsex | prof | depart | +-----+--------------+------+------+--------------+-----------+ | 001 | 曾志高翔 | 18 | 1 | 教学总监 | 语言系 | | 002 | 徐亮伟 | 50 | 1 | 讲师 | 文学系 | | 003 | 李永义 | 80 | 1 | 助教 | 科学系 | | 004 | 曾志高翔 | 18 | 1 | 教学总监 | 语言系 | | 005 | 徐亮伟 | 50 | 1 | 讲师 | 文学系 | | 006 | 李永义 | 80 | 1 | 助教 | 科学系 | +-----+--------------+------+------+--------------+-----------+ 6 rows in set (0.00 sec) mysql> select distinct(depart) from teacher; +-----------+ | depart | +-----------+ | 语言系 | | 文学系 | | 科学系 | +-----------+ 3 rows in set (0.00 sec)

3.查询student表的所有记录。

mysql> select * from student; +-----+--------+------+------+---------------------+-------+ | sno | sname | sage | ssex | sbirthday | class | +-----+--------+------+------+---------------------+-------+ | 1 | 徐导 | 20 | 1 | 2022-08-14 16:22:39 | 1 | | 2 | 曾导 | 18 | 1 | 2022-08-14 16:22:39 | 1 | | 3 | 李导 | 25 | 1 | 2022-08-14 16:22:39 | 2 | +-----+--------+------+------+---------------------+-------+ 3 rows in set (0.00 sec)

4.查询score表中成绩在60到80之间的所有记录。

mysql> select * from score where mark>=60 and mark<=80; +-----+-----+------+ | sno | cno | mark | +-----+-----+------+ | 3 | 1 | 60.0 | | 3 | 2 | 80.0 | | 2 | 3 | 60.0 | +-----+-----+------+ 3 rows in set (0.00 sec)

5.查询score表中成绩为85，86或88的记录。

mysql> select * from score where mark in (85,86,88); +-----+-----+------+ | sno | cno | mark | +-----+-----+------+ | 3 | 3 | 88.0 | +-----+-----+------+ 1 row in set (0.00 sec)

6.查询student表中4班或性别为“女”的同学记录。

mysql> select * from student where class='1' or ssex='0'; +-----+--------+------+------+---------------------+-------+ | sno | sname | sage | ssex | sbirthday | class | +-----+--------+------+------+---------------------+-------+ | 1 | 徐导 | 20 | 1 | 2022-08-14 16:22:39 | 1 | | 2 | 曾导 | 18 | 1 | 2022-08-14 16:22:39 | 1 | +-----+--------+------+------+---------------------+-------+ 2 rows in set (0.00 sec)

7.以class降序查询Student表的所有记录。

mysql> select * from student order by class desc; +-----+--------+------+------+---------------------+-------+ | sno | sname | sage | ssex | sbirthday | class | +-----+--------+------+------+---------------------+-------+ | 3 | 李导 | 25 | 1 | 2022-08-14 16:22:39 | 2 | | 1 | 徐导 | 20 | 1 | 2022-08-14 16:22:39 | 1 | | 2 | 曾导 | 18 | 1 | 2022-08-14 16:22:39 | 1 | +-----+--------+------+------+---------------------+-------+ 3 rows in set (0.00 sec)

8.以cno升序、mark降序查询Score表的所有记录

mysql> select * from score order by cno , mark desc; +-----+-----+------+ | sno | cno | mark | +-----+-----+------+ | 1 | 1 | 90.0 | | 3 | 1 | 60.0 | | 2 | 1 | 10.0 | | 2 | 2 | 99.5 | | 1 | 2 | 90.0 | | 3 | 2 | 80.0 | | 3 | 3 | 88.0 | | 1 | 3 | 80.5 | | 2 | 3 | 60.0 | +-----+-----+------+ 9 rows in set (0.00 sec)

9.查询4班的学生人数。

mysql> select count(class) from student where class='2' group by class; +--------------+ | count(class) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)

10.查询”曾导“教师任课的学生成绩。

## 传统连接 mysql> select teacher.tname,course.cname,student.sname,score.mark -> from teacher,course,student,score -> where teacher.tno=course.tno -> and course.cno=score.cno -> and student.sno=score.sno -> and teacher.tname='曾志高翔'; ## 内连接 mysql> select teacher.tname,course.cname,student.sname,score.mark -> from teacher join course -> on teacher.tno=course.tno -> join score -> on course.cno=score.cno -> join student -> on student.sno=score.sno -> where teacher.tname='曾志高翔'; +--------------+--------+--------+------+ | tname | cname | sname | mark | +--------------+--------+--------+------+ | 曾志高翔 | 英语 | 徐导 | 90.0 | | 曾志高翔 | 英语 | 曾导 | 10.0 | | 曾志高翔 | 英语 | 李导 | 60.0 | +--------------+--------+--------+------+ 3 rows in set (0.00 sec)

11.查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。

## 传统连接 mysql> select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark -> from teacher,course,student,score -> where teacher.tno=course.tno -> and course.cno=score.cno -> and student.sno=score.sno -> and student.ssex='1' -> and course.cname='语文'; ## 内连接 mysql> select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark -> from teacher join course -> on teacher.tno=course.tno -> join score -> on course.cno=score.cno -> join student -> on student.sno=score.sno -> where student.ssex='1' -> and course.cname='语文'; +-----------+--------+-----------+--------+--------+------+ | tname | prof | depart | sname | cname | mark | +-----------+--------+-----------+--------+--------+------+ | 徐亮伟 | 讲师 | 文学系 | 徐导 | 语文 | 90.0 | | 徐亮伟 | 讲师 | 文学系 | 曾导 | 语文 | 99.5 | | 徐亮伟 | 讲师 | 文学系 | 李导 | 语文 | 80.0 | +-----------+--------+-----------+--------+--------+------+ 3 rows in set (0.00 sec)

12.把11题查出的成绩按照降序排序。

mysql> select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark -> from teacher join course -> on teacher.tno=course.tno -> join score -> on course.cno=score.cno -> join student -> on student.sno=score.sno -> where student.ssex='1' -> and course.cname='语文' -> order by score.mark desc; +-----------+--------+-----------+--------+--------+------+ | tname | prof | depart | sname | cname | mark | +-----------+--------+-----------+--------+--------+------+ | 徐亮伟 | 讲师 | 文学系 | 曾导 | 语文 | 99.5 | | 徐亮伟 | 讲师 | 文学系 | 徐导 | 语文 | 90.0 | | 徐亮伟 | 讲师 | 文学系 | 李导 | 语文 | 80.0 | +-----------+--------+-----------+--------+--------+------+

标签：聚合工具

暂时没有评论，来抢沙发吧~

CentOS MySQL语句入门

linux cpu占用率如何看

宝塔数据库如何清理缓存

oracle怎么创建存储过程

推荐文章

api接口有哪几种分类及功能

什么是API接口?API接口简单介绍

短信API接口概述，短信API接口的优势

7款快递物流的物流查询API工具，物流快递查询API接口怎么对接？

企业四要素: 了解企业经营成功的关键

什么是语音验证码?,语音验证码平台有哪些

全国工商查询系统怎么查企业名录

哪些平台提供实名认证的接口？

PHP如何调用API接口?

如何使用百度天气预报API接口?

最近发表

热评文章

数据接口api（数据接口API开发平台）

数据开放接口api（数据服务api开发）

Python爬虫教程：爬取酷狗音乐（python爬取

hbuilder怎么更改字体大小和颜色

直播平台api接口 - 构建卓越的直播平台

实时股票数据api接口（股票实时行情api接口）