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 "[1]";
mysql> SELECT * FROM tutors WHERE Tname RLIKE "ang";
IS NULL/IS NOT NULL
mysql> SELECT * FROM students WHERE TID IS NULL;
2、按指定的字段内容排序
ORDER BY <col_name> [ASC|DESC]
DESC:降序
ASC:升序,默认
mysql> SELECT * FROM tutors ORDER BY Age;
mysql> SELECT * FROM tutors ORDER BY Age DESC;
3、限制结果的输出行数
LIMIT [n,]m
忽略前n行,共显示m行
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、按指定字段将数据进行分组
GROUP BY <字段名称> [HAVING <条件>]
聚合函数:
SUM(字段名称) 求和
AVG(字段名称) 平均值
MAX(字段名称) 最大值
MIN(字段名称) 最小值
COUNT(字段名称) 计数
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;
连接查询(多表查询)
内连接
外连接
左外连接
右外连接
前提条件:多张表之间要存在相关联的字段
内连接
特征:只有相关联字段具有相同的值时,才显示对应的结果
语法:
SELECT tb1.col, tb2.col,.... FROM tb1 INNER JOIN tb2 ON tb1.col=tb2.col
mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname
-> FROM students INNER JOIN courses
-> ON students.CID1=courses.CID;
mysql> SELECT students.Name, students.Age, students.Gender, tutors.Tname
-> FROM students INNER JOIN tutors
-> ON students.TID=tutors.TID;
mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname, tutors.Tname
-> FROM students INNER JOIN courses INNER JOIN tutors
-> ON students.CID1=courses.CID AND courses.TID=tutors.TID;
自然连接
mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname
-> FROM students,courses
-> WHERE students.CID1=courses.CID;
外连接
左外连接
特征:以左表为主,显示左表所有数据,右表中没有关联的数据时,显示为NULL
语法:
SELECT tb1.col, tb2.col,.... FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
mysql> SELECT students.Name, students.Age, students.Gender, courses.Cname
-> 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@%
- 创建用户
CREATE USER
mysql> CREATE USER 'martin'@'localhost' IDENTIFIED BY 'Www.1.com';
mysql> FLUSH PRIVILEGES;
mysql> SELECT user();
+------------------+
| user() |
+------------------+
| martin@localhost |
+------------------+
1 row in set (0.00 sec)
- 设置用户的密码
方法1)
mysql> SET PASSWORD FOR 'martin'@'localhost' = PASSWORD("Www.3.com");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
方法2)
[root@node01 mysql]# mysqladmin -u martin -p password "Www.4.com"
方法3)
mysql> ALTER USER 'martin'@"localhost" IDENTIFIED BY "Www.2.com";
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
- 存放用户信息的表 ------- mysql.user
mysql> SELECT User,Host,authentication_string FROM mysql.user;
+-----------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | *559EEE7F74FC37F4FAD46A371DB9BB7FCFFEC07E |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| martin | localhost | *E98BC2494435DF70D164D506EB319CD2FF595431 |
+-----------+-----------+-------------------------------------------+
- 删除用户
mysql> DROP USER 'martin'@'localhost';
mysql> FLUSH PRIVILEGES;
权限控制
- 查看用户权限
mysql> SHOW GRANTS FOR 'martin'@'localhost';
- 授权
GRANT <权限>,... ON <库名>.<表名> TO <用户名> [IDENTIFIED BY "password"]
权限:
select
select,update,create
all
库名.表名:
jiaowu.tutors
jiaowu.*
*.*
mysql> GRANT select ON jiaowu.tutors TO 'martin'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> GRANT select,insert ON jiaowu.* TO 'admin'@'localhost' IDENTIFIED BY 'Www.1.com';
mysql> FLUSH PRIVILEGES;
示例:建立远程登录用户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";
mysql> FLUSH PRIVILEGES;
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 ↩︎