mysql-SQL语句的使用(第八章)
SQL语句
DDL 数据定义语言
create, drop, alter
DML 数据操纵语言
insert, delete, select, update
DCL 数据控制语言
grant, revoke
使用ALTER TABLE修改表结构
- 修改表名称
ALTER TABLE <表名> RENAME <新表名>
mysql> ALTER TABLE game_account RENAME account;
- 修改表的存储引擎
mysql> ALTER TABLE account ENGINE=MyISAM;
- 添加字段
ALTER TABLE <表名> ADD <字段名称> <字段定义>
mysql> ALTER TABLE account ADD Account_gender ENUM("M","F") NOT NULL;
mysql> ALTER TABLE account ADD Game_zone VARCHAR(20) NOT NULL DEFAULT "HuaBei" FIRST;
mysql> ALTER TABLE account ADD Account_money BIGINT AFTER Account_level;
- 删除字段
ALTER TABLE <表名> DROP <字段名称>
mysql> ALTER TABLE account DROP Account_role ;
- 修改字段名称及字段定义
ALTER TABLE <表名> CHANGE <旧字段名称> <新字段名称> <字段定义>
mysql> ALTER TABLE account CHANGE Account_password password CHAR(25) NOT NULL;
mysql> ALTER TABLE account CHANGE password password VARCHAR(60) ;
- 修改字段定义
ALTER TABLE <表名> MODIFY <字段名称> <字段定义>
mysql> ALTER TABLE account MODIFY Account_name VARCHAR(20) NOT NULL;
DML 数据操纵语言
1、添加数据 INSERT INTO
INSERT INTO tb_name(字段1名称,字段2名称,....) VALUES(value1,value2,....)
注意:
字段的数据类型是字符型、日期/时间型,对应的值需要使用双引号、单引号
- 插入单条数据
mysql> INSERT INTO account(Account_name,Account_password, Account_level) VALUES("king","123456",60);
mysql> SELECT * FROM account;
+--------------+------------------+---------------+---------------+----------------+
| Account_name | Account_password | Account_level | Account_money | Account_gender |
+--------------+------------------+---------------+---------------+----------------+
| king | 123456 | 60 | NULL | M |
+--------------+------------------+---------------+---------------+----------------+
1 row in set (0.00 sec)
- 插入多条数据
mysql> INSERT INTO account(Account_name ,Account_password, Account_level) VALUES("queen","123",1),("son", "redhat", 2),("sunzi","456",1);
- 向表中所有字段插入数据
mysql> INSERT INTO account VALUES("haha","linux",10,25000000,"F");
2、删除数据
DELETE FROM <tb_name> WHERE
mysql> DELETE FROM account WHERE Account_name="haha";
3、更新数据
UPDATE <tb_name> SET <字段名称>=<新值> WHERE <条件>
mysql> UPDATE account SET Account_password = "1q2w3e4r..1" WHERE Account_name = "king";
数据查询 --- SELECT
单表查询
多表查询/连接查询
子查询/嵌套查询
导入jiaowu数据库
[root@node01 ~]# mysql -u root -p < jiaowu.sql
Enter password:
单表查询
SELECT col_name1, col_name2, .... FROM tb_name [select_statement]
mysql> SELECT * FROM tutors;
mysql> SELECT Tname,Age FROM tutors;
mysql> SELECT Tname AS 教师, Age AS 年龄 FROM tutors; >>>设置字段的别名
mysql> SELECT Tname 教师, Age 年龄 FROM tutors;
select_statement查询子句:
1、按指定的条件查询数据
WHERE condition
条件的写法:
数字操作符: =, !=, >, >=, <, <=
逻辑操作符: AND, OR
mysql> SELECT * FROM tutors WHERE Age > 80;
mysql> SELECT * FROM tutors WHERE Age >= 70 AND Age <= 80;
BETWEEN n AND m
mysql> SELECT * FROM tutors WHERE Age BETWEEN 70 AND 80;
mysql> SELECT * FROM tutors WHERE Tname = "OuYangfeng" OR Tname = "YiDeng" OR Tname = "HuYidao";
IN(value1,value2,value3)
mysql> SELECT * FROM tutors WHERE Tname IN("OuYangfeng","YiDeng","HuYidao");
模糊查询
方法1)
LIKE "通配符表达式"
通配符:
% 任意长度任意字符
_ 任意单个字符
mysql> SELECT * FROM tutors WHERE Tname LIKE "%ang%";
mysql> SELECT * FROM tutors WHERE Tname LIKE "Y%" OR Tname LIKE "H%";
方法2)
RLIKE "正则表达式"
mysql> SELECT * FROM tutors WHERE Tname RLIKE "
mysql> SELECT * FROM tutors WHERE Tname RLIKE "ang"; IS NULL/IS NOT NULL 2、按指定的字段内容排序 mysql> SELECT * FROM tutors ORDER BY Age; mysql> SELECT * FROM tutors ORDER BY Age DESC; 3、限制结果的输出行数 mysql> SELECT * FROM tutors LIMIT 3; mysql> SELECT * FROM tutors LIMIT 2,3; mysql> SELECT * FROM tutors ORDER BY Age LIMIT 1; mysql> SELECT * FROM tutors WHERE Gender="M" ORDER BY Age DESC LIMIT 1; 4、按指定字段将数据进行分组 聚合函数: mysql> SELECT AVG(Age) AS 平均年龄 FROM tutors; mysql> SELECT AVG(Age) AS 平均年龄,Gender AS 性别 FROM tutors GROUP BY Gender; mysql> SELECT AVG(Age) AS 平均年龄,Gender AS 性别 FROM tutors GROUP BY Gender HAVING 平均年龄>65; 连接查询(多表查询) 前提条件:多张表之间要存在相关联的字段 内连接 mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname mysql> SELECT students.Name, students.Age, students.Gender, tutors.Tname mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname, tutors.Tname 自然连接 mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname 外连接 左外连接 mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname 右外连接 嵌套查询/子查询 用户权限控制 用户管理 CREATE USER mysql> CREATE USER 'martin'@'localhost' IDENTIFIED BY 'Www.1.com'; mysql> SELECT user(); 方法1) mysql> SET PASSWORD FOR 'martin'@'localhost' = PASSWORD("Www.3.com"); mysql> FLUSH PRIVILEGES; 方法2) [root@node01 mysql]# mysqladmin -u martin -p password "Www.4.com" 方法3) mysql> ALTER USER 'martin'@"localhost" IDENTIFIED BY "Www.2.com"; mysql> FLUSH PRIVILEGES; mysql> SELECT User,Host,authentication_string FROM mysql.user; mysql> DROP USER 'martin'@'localhost'; mysql> FLUSH PRIVILEGES; 权限控制 mysql> SHOW GRANTS FOR 'martin'@'localhost'; GRANT <权限>,... ON <库名>.<表名> TO <用户名> [IDENTIFIED BY "password"] mysql> GRANT select ON jiaowu.tutors TO 'martin'@'localhost'; mysql> GRANT select,insert ON jiaowu.* TO 'admin'@'localhost' IDENTIFIED BY 'Www.1.com'; 示例:建立远程登录用户alice,允许在192.168.122.137上登录数据库服务器,允许其查询jiaowu库中的tutors表,并允许其更新表中的年龄字段 mysql> GRANT select, update(Age) ON jiaowu.tutors TO 'alice'@'192.168.122.137' IDENTIFIED BY 'Www.1.com'; mysql> FLUSH PRIVILEGES; 客户端: [root@node03 ~]# yum install -y mysql-community-client.x86_64 [root@node03 ~]# mysql -u alice -p -h 192.168.122.105 REVOKE <权限> ON <库名>.<表名> FROM <用户名> mysql> REVOKE select ON jiaowu.tutors FROM "martin"@"localhost"; https://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html 索引Index 1、创建索引 mysql> CREATE INDEX account_name ON account(Account_name); 2、查看索引 mysql> SHOW INDEX FROM accountG; 3、删除索引 mysql> DROP INDEX account_name ON account; HY ↩︎mysql> SELECT * FROM students WHERE TID IS NULL;
ORDER BY <col_name> [ASC|DESC]
DESC:降序
ASC:升序,默认
LIMIT [n,]m
忽略前n行,共显示m行
GROUP BY <字段名称> [HAVING <条件>]
SUM(字段名称) 求和
AVG(字段名称) 平均值
MAX(字段名称) 最大值
MIN(字段名称) 最小值
COUNT(字段名称) 计数
内连接
外连接
左外连接
右外连接
特征:只有相关联字段具有相同的值时,才显示对应的结果
语法:
SELECT tb1.col, tb2.col,.... FROM tb1 INNER JOIN tb2 ON tb1.col=tb2.col
-> FROM students INNER JOIN courses
-> ON students.CID1=courses.CID;
-> FROM students INNER JOIN tutors
-> ON students.TID=tutors.TID;
-> FROM students INNER JOIN courses INNER JOIN tutors
-> ON students.CID1=courses.CID AND courses.TID=tutors.TID;
-> FROM students,courses
-> WHERE students.CID1=courses.CID;特征:以左表为主,显示左表所有数据,右表中没有关联的数据时,显示为NULL
语法:
SELECT tb1.col, tb2.col,.... FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
-> FROM students LEFT JOIN courses
-> ON students.CID2=courses.CID; 特征:以右表为主,显示右表所有数据,左表中没有关联的数据时,显示为NULL
语法:
SELECT tb1.col, tb2.col,.... FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
以查询的结果作为另外一个查询的条件、数据源使用
mysql> SELECT * FROM tutors WHERE Age > (SELECT AVG(Age) FROM tutors);
格式:user@host
root@localhost
host写法:
1、IP地址 martin@192.168.1.1
2、网段 martin@192.168.1.%
3、所有 martin@%
mysql> FLUSH PRIVILEGES;
+------------------+
| user() |
+------------------+
| martin@localhost |
+------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+-----------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | *559EEE7F74FC37F4FAD46A371DB9BB7FCFFEC07E |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| martin | localhost | *E98BC2494435DF70D164D506EB319CD2FF595431 |
+-----------+-----------+-------------------------------------------+
权限:
select
select,update,create
all
库名.表名:
jiaowu.tutors
jiaowu.*
*.*
mysql> FLUSH PRIVILEGES;
mysql> FLUSH PRIVILEGES;
mysql> FLUSH PRIVILEGES;作用:提高查询速度