MySQL user management and SQL Getting Started

1th Chapter  mysql User Management:1.1  user's definition:   user name + host domain   mysql> select user,host,password  from mysql.user;+------+-----------+----------+| user | host       | password |+------+-----------+----------+| root | localhost |           | |  root | db01      |           | |  root | 127.0.0.1 |          |1.2  The role of the user:1.       the objects (tables and libraries) used to log in to the database 2.       manage Data description: analogy: Create a user in Linux, change the permissions of a directory or file to manage the data 1.2.1  create a user rights setting: grant  permissions  on  permissions range  to  users   identified by  ' Password ';1.2.2  Rights Management: Read and write to the database, etc., permissions can be used to manage what a user may do to the database (Insert update, Select, delete, drop, create, and so on) 1.2.3  role: Read, write, etc. on the database (InseRt;update;select) 1.2.4  permission range:1.       full Library level: *.*2.        single-Library level: test.*3.       single table level: test.table_name1.2.5  User:     ' clsn ' @ ' localhost '    local      ' clsn ' @ ' 192.168.66.149 '      ' clsn ' @ ' 192.168.66.% ' clsn ' @ ' 192.168.66.14% ' 1.3  exercise: Create a user user as required only by 10.0.0.0/ 24 network segment access, the user name is Jiang, the password is 123jiang the user can only add insert;create to the object under the Jiang database; change the update; select1.3.1  create a user and authorize grant  insert,select,create,update on jiang.* to  ' [email protected]% '  identified  by  ' 123 ';1.3.2  View user's permissions: mysql> show grants for [email protected] ' 10.0.0.% ' \g*************************** 1. row ***************************grants for [email  protected]%: GRANT ALL PRIVILEGES ON *.* TO  ' root ' @ ' 10.0.0.% '   Identified by password&nBSP; ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' 1 row in set  (0.01 sec) 1.3.3  standard method for creating a user :create user  ' web ' @ ' 172.16.1.% '  identified by  ' web123 ';    This creates a user with only the permission to connect 1.3.4  delete User: mysql> drop user [email protected] ' 10.0.0.% ';1.3.5  Reclaim permissions revoke insert on *.* from [email protected] ' Localhos ';1.4  MySQL forgot password modification method:1.       to stop the database/etc/init.d/mysqld stop2.        Disable the connection layer's authorization function and remote login kinetic energy, and start cd /application/mysql/bin/mysqld_safe --skip-grant-table  --user=mysql --skip-networking & Description: This startup mode, no password login, network users can not log on, can only log on locally, and authorization related to the command can not be executed 3.        Direct Change Password Mysql> update mysql.user set password=password (' 123 ')  where user= ' root '  and host= ' localhost ';mysql> flush privileges; MySQL5.7 Version Change Password change field:  authentication_string4.       quit, restart the service, normal restart can, I failed here, so first stop at the start/etc/init.d/mysqld stop/etc/init.d/ mysqld start5.       logging into the database for verification mysql -uroot -p123 Chapter 2nd   MySQL client tools and SQL Getting Started 2.1 mysql client commands what are the?1.      mysql      ---Used to connect to the database---Send the user's SQL statements to the server 2.      mysqladmin---the command-line administration tool 3.       mysqldump---Back up the contents of the Database and table 2.2 mysql command features:1.       User connection database 2.       for managing Database 2.2.1 mysql Command Interface features: Command      command description     \h   help  or  ?      get help      \G     formatted output (row to column)     \T  or  tee     Logging operation logs   tee /tmp/mysql.log    \c  or  CTRL+c     Exit mysql    \s  or  status     View database status information     \.  or  source     mysql> source   /tmp/world.sql    \!     Using commands in the shell  mysql> \!   cat /etc/redhat-releaseCentOS    release 6.9  (Final)      \u  or use        use  worldshow   databases   see the names of all current databases Show   tables     View all tables in the current use to database show  tables   from world    View tables      shortcuts under Target database      page DOWN, tab, ctrl   +c , CTRL Help in  +l    2.2.2 mysql command: Contents see the full list of SQL categories help mysql> help  Contents view Help for a specific SQL category or statement mysql> help account management; view the grant help mysql> help  Grant state-related SQL statements help MYSQL&GT;&NBSP;HELP&NBsp;statusmysqladmin Command Description: Feature options      description     mysqladmin -u user  -p password  ping     Mandatory response   (ping) server.     mysqladmin -u user  -p password  shutdown     shut down the server.     mysqladmin -u User  -p Password  create   databasename      Create a database.     mysqladmin -u User  -p Password drop   databasename      Delete database     mysqladmin -u user  -p password  version     Display server and version information     mysqladmin -u user  -p password  status     Show or reset server state variables     mysqladmin -u user  -p password  password     set password     mysqladmin -u user  -p password  flush-privileges     re-refresh authorization form.     mysqladmin -u User  -p Password  flush-logs     refreshes the log file and cache. Getting Started with      2.3 sql statements:2.3.1 ddl:    data definition language definition scope: Features of library names and libraries         view databases for table names and columns in tables view all databases mysql> show databases;+--------------------+|  database           |+--------------------+|  information_schema | |  mysql              | |  performance_schema | |  test               | |  world              | |  zabbix             |+------------------ --+ View Current database Mysql> select database (); +------------+| database ()  |+------------+|  zabbix     |+------------+1 row in set  (0.00&NBSP;SEC) Create a library of operations on the database mysql> create database zabbix character set utf8; query ok, 1 row affected  (0.01 sec) to view the tables in the library mysql> show tables;+------ ------------+| tables_in_zabbix |+------------------+| stu               |+------------------+1 row in set  (0.00 &NBSP;SEC) View the creation statement for the library mysql> show create database zabbix;  +----------+---------- -------------------------------------------------------+| database | create database                                                    |+----------+------------------------- ----------------------------------------+| zabbix   | create database  ' Zabbix '  /*!40100  default character set utf8 */ |+----------+------------------------------------ -----------------------------+1 row in set  (0.00 sec) modifies the properties of the library: only the character set and proofing rules can be modified mysql>  alter database zabbix charset utf8mb4; Delete a library:mysql> drop database  Zabbix; Çeku mysql> use zabbix; Create a table for the operation of the table mysql> create table stu  (Id int, Name varchar (+), age int ,gender int); View the table's creation statement mysql> show create table  stu; added in the last column of the table: Mysql> alter table stu add addr varchar (20); Add the header to the table: MySQL > alter table stu add stu_id int first; add after a column: Mysql> alter  table stu add qq int after name; add Tel_num after age, add emailmysql>  on the last line Alter table stu add tel_num int after age,add email varchar (20); Delete a column: Mysql> alter  table stu drop email; Modify Column Name: MYSQL&GT;&NBSP;ALTER&NBSP;TABLE&NBSP;STU&NBSP;CHANGE&NBSP;QQ  QQ int; Modifying the data type of a column: Mysql> alter table stu modify gender varchar (20); Create an empty table with the same table structure mysql> create table stu_0 like stu;mysql> show tables;+-- ----------------+| tables_in_zabbix |+------------------+| stu               | |  stu_0            |+------------------+2  rows in set  (0.00&NBSP;SEC) Create a backup table with the same table structure mysql> create table stu_0  as select * from stu;2.3.2 dcl       Database Control Language User authorization grant  ALL ON *.* TO  ' jiang ' @ ' localhost '; Show grants&nBsp for  ' jiang ' @ ' localhost ' \g to create a user while authorizing grant insert,select,create,update on jiang.* to  [email protected] ' 10.0.0.% '  identified by  ' 123 '; Reclaim permissions revoke insert on  *.* from [email protected];2.3.3 dml       Data Line Operation language (add and revise) Insert statement specifies that columns are inserted into mysql> insert into stu  (STU_ID,QQ)  values (1,777); All columns are inserted mysql>  Insert into stu values (2,777, ' J ',,, ' Hao ', 2, ' R ', ' Q ', 1); Insert Copy table structure and contents---Table already exists, in the case where MySQL can be replicated > create table stu_1 like stu;mysql> insert into stu_1  select * from stu; Modify and delete operations Delete data: mysql> delete from stu where stu_id=1          Tombstone mysql> truncate table stu_3;                   physically delete the modified data, To add a Where condition mysql> update&Nbsp;stu_3 set qq=5656 where age= ' Hao ';d elete     After deletion, you can use the binary log to reverse the Insert command to retrieve the data to truncate    physical deletion, the data will not be returned to the general deletion of large tables, first truncate and then drop the entire table, Efficiency will be higher use update instead of Delete, pseudo-delete Mysql> alter table stu add state int default  1; query ok, 0 rows affected  (0.54 sec) records: 0  duplicates:  0  warnings: 0 mysql> select * from stu;+--------+------+-------+-- ---------+------+---------+--------+------+-------+| stu_id | id   | name   | qq        | age  | tel_num  | gender | addr | state |+--------+------+-------+-----------+------+----- ----+--------+------+-------+|      1 |  123 | jiang  |       222 | ni   |   15555 | boy    | hao   |     1 | |       1 |  123 | jiang |        222 | ni   |   15555 | boy     | hao  |     1 | |       1 |  123 | jiang |        222 | ni   |   15555 | boy     | hao  |     1 | |       7 | null | da ya | 850144102 |  NULL |    NULL | NULL   | NULL |      1 |+--------+------+-------+-----------+------+---------+--------+------+-------+4 rows in set  (0.00 sec) Modify the state value equal to 0 for delete, mysql> update stu set state=0 where name= ' Jiang ';mysql>  select * from stu where state=1;+--------+------+-------+-----------+------+--- ------+--------+------+-------+|&NBSP;STU_ID&NBSP;|&NBSP;ID&NBSP;&NBSP;&NBSP;|&NBSP;NAME&NBSP;&NBSP;|&NBSP;QQ         | age  | tel_num | gender |  addr | state |+--------+------+-------+-----------+------+---------+--------+------+----- --+|      7 | null | da ya | 850144102 |  NULL |    NULL | NULL   | NULL |      1 |+--------+------+-------+-----------+------+---------+--------+------+-------+1  row in set  (0.01&NBSP;SEC) to prevent accidental deletion:   -u, --safe-updates  only allow update  and delete that uses keys.mysql command Plus-u     when executing update and DELETE statements No where condition does not execute 2.3.4&NBSP;DQL: data row Query Language Select query statement view all information in the Stu table mysql> select * from stu;      View information for a column mysql> select name from stu;      Multiple columns use a comma-delimited view of the QQ number with ID number 777 Mysql> select qq from stu where id=777;where clause using:   Filter the data according to the specified conditions filtering:mysql> select stu_id from stu where qq=777; comparison filter: mysql> select stu_id from stu where qq<777; SQL filtering exercises: Sorting: In the order of the population from less to more: select name,population from citywhere countrycode= ' CHN '   order by population; is displayed in the order that it is sorted from more to less:select name,population from citywhere  Countrycode= ' CHN '  ORDER BY Population DESC; shows top 10 of population rankingsName: Select name,population from citywhere countrycode= ' CHN ' order by population  DESC LIMIT 10; Show population rankings   lines 50th through 60th select name,population from citywhere  Countrycode= ' CHN ' order by population desc limit 50,10; shows cities in the world with a population of less than 100:select  name,population from city where population<1000; Show PCN is that country: select name from  city where country= ' PCN '; shows the state name of the country where the world's population is less than 100 select country.name from  Country,citywhere city. Population<100 and country. Code=city.countrycode; The above commands can be simply written: select  co.name from city as  ci  Country as cowhereci.population<100and co.code=ci. CountryCode;

MySQL user management and SQL Getting Started