• mysql-SQL语句的使用(第八章)


    SQL语句
    DDL 数据定义语言
    create, drop, alter
    DML 数据操纵语言
    insert, delete, select, update
    DCL 数据控制语言
    grant, revoke

    使用ALTER TABLE修改表结构

    1. 修改表名称

    ALTER TABLE <表名> RENAME <新表名>

    mysql> ALTER TABLE game_account RENAME account;

    1. 修改表的存储引擎

    mysql> ALTER TABLE account ENGINE=MyISAM;

    1. 添加字段

    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;

    1. 删除字段

    ALTER TABLE <表名> DROP <字段名称>

    mysql> ALTER TABLE account DROP Account_role ;

    1. 修改字段名称及字段定义

    ALTER TABLE <表名> CHANGE <旧字段名称> <新字段名称> <字段定义>

    mysql> ALTER TABLE account CHANGE Account_password password CHAR(25) NOT NULL;

    mysql> ALTER TABLE account CHANGE password password VARCHAR(60) ;

    1. 修改字段定义

    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,....)

    注意:
    字段的数据类型是字符型、日期/时间型,对应的值需要使用双引号、单引号

    1. 插入单条数据

    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)

    1. 插入多条数据

    mysql> INSERT INTO account(Account_name ,Account_password, Account_level) VALUES("queen","123",1),("son", "redhat", 2),("sunzi","456",1);

    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@%
    
    1. 创建用户

    CREATE USER IDENTIFIED BY

    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. 设置用户的密码

    方法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)

    1. 存放用户信息的表 ------- 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 |
    +-----------+-----------+-------------------------------------------+

    1. 删除用户

    mysql> DROP USER 'martin'@'localhost';

    mysql> FLUSH PRIVILEGES;

    权限控制

    1. 查看用户权限

    mysql> SHOW GRANTS FOR 'martin'@'localhost';

    1. 授权

    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

    1. 撤销权限

    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;


    1. HY ↩︎

    有志者,事竟成,破釜沉舟,百二秦关终属楚; 苦心人,天不负,卧薪尝胆,三千越甲可吞吴。 想到与得到中间还有两个字——做到。
  • 相关阅读:
    SpringMVC常用注解
    SpringMVC基础知识
    如何在git中恢复先前的提交?
    git pull 和git fetch的区别
    webpack和gulp的比较
    SpringMVC框架
    Spring框架
    为什么要在一个团队中开展软件测试工作?
    需求测试的注意事项有哪些?
    主键、外键的作用,索引的优点与不足?
  • 原文地址:https://www.cnblogs.com/huoxc/p/13047811.html
Copyright © 2020-2023  润新知