MySQL statement collection: creation, authorization, query, modification

1. User creation, permissions, deletion

1. Connect MySql operation

Connection: mysql -h host address -u username -p user password (Note: u and root do not need spaces, the same applies to others)

Disconnect: exit (return Car)

Open cmd, enter

mysql -h 127.0.0.1 -u root -p and enter the password. You can connect to the local MySql database.

2. Create user:

Command: CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Instructions:

username - the user name you will create,

host - specify the host on which the user can log in. If it is a local user, localhost can be used. If you want The user can log in from any remote host and can use the wildcard %.

password - the user's login password. The password can be empty. If it is empty, the user can log in to the server without a password.

Example:

CREATE USER 'lin'@'localhost' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY ''; 
CREATE USER 'pig'@'%';

When logging in, first exit the current one, and then enter the following

mysql -h 127.0.0.1 -u linlin -p 密码
mysql -h 127.0.0.1 -u pig -p 密码

3. Authorization:

Command: GRANT privileges ON databasename.tablename TO 'username'@'host'

Description:

privileges - user's operation permissions, such as SELECT, INSERT, UPDATE, etc. (details See the list at the end of this article). If you want to grant all permissions, use ALL.; databasename - database name, tablename - table name. If you want to grant the user the corresponding operation permissions on all databases and tables, you can use *, such as * .*.

Example:

GRANT SELECT, INSERT ON school.* TO 'lin' @'%';
GRANT ALL ON *.* TO 'pig'@'%';

Note: A user authorized with the above command cannot authorize other users. If you want the user to be able to authorize, use the following command:

GRANT privileges ON databasename.tablename TO 'username'@'host'WITH GRANT OPTION;

4. Set and change user password

Command: SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword'); If it is the currently logged in user, use SET PASSWORD = PASSWORD ("newpassword");

Example: SET PASSWORD FOR 'lin'@'%' = PASSWORD("123456");

5. Revoke user privileges

Command: REVOKE privilege ON databasename.tablename FROM 'username'@'host';

Description: privilege, databasename, tablename - the same as the authorization part.

例子: REVOKE SELECT ON *.* FROM 'pig'@'%';

注意: 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select 权限.

具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看.

6、删除用户

命令: DROP USER 'username'@'host';

二、数据库与表显示、创建、删除

1、数据库显示、创建、删除

显示数据库：show databases;

创建库：create database 库名;

删除库：drop database 库名; 

使用库(选中库)：use 库名;

2、表显示、创建、删除

显示数据表：show tables; （要先用use 数据库名选定数据库）

显示表结构：describe 表名;或者desc 表名

创建表：create table 表名 (字段设定列表);

CR EATE TABLE
USER
(
name VARCHAR(30) NOT NULL,
id INT DEFAULT '0' NOT NULL,
stu_id INT,
phone VARCHAR(20),
address VARCHAR(30) NOT NULL,
age INT(4) NOT NULL,
PRIMARY KEY (name),
CONSTRAINT stu_id UNIQUE (stu_id)
)
ENGINE=InnoDB D EFAULT CHARSET=utf8;

删除表：drop table 表名;

句法：DROP DATABASE [IF EXISTS] db_name

功能：DROP DATABASE删除数据库中的所有表和数据库。要小心地使用这个命令！

DROP DATABASE返回从数据库目录被删除的文件的数目。通常，这3倍于表的数量，因为每张表对应于一个“.MYD”文件、一个“.MYI”文件和一个“.frm”文件。

在MySQL 3.22或以后版本中，你可以使用关键词IF EXISTS阻止一个错误的发生，如果数据库不存在。

三、表复制及备份还原

假设现在有表books：

1.复制表结构

1.1 含有主键等信息的完整表结构

CREATE table 新表名 LIKE book;

1.2 只有表结构，没有主键等信息

create table new table name select * from books;

or

create table new table name as(select * from book);

or

create table New table name select * from books where1=2;

2. Pour the data from the old table into the new table

INSERT INTO New table SELECT * FROM old table;

Note: The new table must already exist

3. Enter the DDL statement to create the table

show create table table name;

4. Clear table data

truncate table table name;

5.Back up database

For example, back up the library database

Go to the bin directory of Mysql

E:\mysql-5.6.23-win32\bin

Use "mysqldump-u username-p database name >Backup name"Export database to file

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -u root -p test >test.sql

Enter password: ***

That’s it.

E:\mysql-5.6.23-win32\bin directory

6. Restore the database

Restore the test database as an example

First create database test1

Then download mysql>Next

Enter the source path

.

Pay attention to the path where test.sql is located!

4. Data operations in the database table

1. Clear the data in the mysql table

delete from table name;

truncate table table name;

The delete statement without where parameter can delete all the contents in the mysql table. Using truncate table can also clear all the contents in the mysql table. content.

效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。

delete的效果有点像将mysql表中所有记录一条一条删除到删完，

而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

2、删除表中的某些数据

delete from命令格式：delete from 表名 where 表达式

例如，删除表 MyClass中编号为1 的记录：

代码如下:

mysql> delete from MyClass where id=1;

五、修改表的列与表名

1、给列更名

>alter table 表名称 change 字段名称 字段名称

例如：

alter table pet change weight wei;

2、给表更名

>alter table 表名称 rename 表名称

例如：

alter table tbl_name rename new_tbl

3、修改某个表的字段类型及指定为空或非空

>alter table 表名称 change 字段名称字段名称 字段类型 [是否允许非空];

>alter table 表名称 modify 字段名称字段类型 [是否允许非空];

4、修改某个表的字段名称及指定为空或非空

>alter table 表名称 change 字段原名称字段新名称 字段类型 [是否允许非空];

例如:

修改表expert_info中的字段birth,允许其为空

代码如下:

>alter table expert_info change birth birth varchar(20) null;

六、修改表中的数据

1.增加一个字段(一列)

alter table table_name add column column_name type default value; type指该字段的类型,value指该字段的默认值

例如:

代码如下:

alter table mybook add column publish_house varchar(10) default ”;

2.更改一个字段名字(也可以改变类型和默认值)

alter table table_name change sorce_col_name dest_col_name type defaultvalue; source_col_name指原来的字段名称,dest_col_name

指改后的字段名称

例如:

代码如下:

alter table Board_Info change IsMobile IsTelphone int(3) unsigned default1;

3.改变一个字段的默认值

alter table table_name alter column_name set default value;

例如:

代码如下:

alter table book alter flag set default '0′;

4.改变一个字段的数据类型

alter table table_name change column column_name column_name type;

例如:

代码如下:

alter table userinfo change column username username varchar(20)

5.向一个表中增加一个列做为主键

alter table table_name add column column_name type auto_increment PRIMARYKEY;

例如:

代码如下:

alter table book add column id int(10) auto_increment PRIMARY KEY;

6.数据库某表的备份,在命令行中输入:

mysqldump -u root -p database_name table_name > bak_file_name

例如:

代码如下:

mysqldump -u root -p f_info user_info > user_info.dat

7.导出数据

select_statment into outfile”dest_file”;

例如:

代码如下:

select cooperatecode,createtime from publish limit 10 intooutfile”/home/mzc/temp/tempbad.txt”;

8.导入数据

load data infile”file_name” into table table_name;

例如:

代码如下:

load data infile”/home/mzc/temp/tempbad.txt” into table pad;

9.将两个表里的数据拼接后插入到另一个表里。下面的例子说明将t1表中的com2和t2表中的com1字段的值拼接后插入到tx表对应的字段里。

例如:

代码如下:

insert into tx select t1.com1,concat(t1.com2,t2.com1) from t1,t2;

10.删除字段

alter table form1 drop column 列名;

七、查询表

mysql查询的五种子句

where(条件查询)、having（筛选）、group by（分组）、order by（排序）、limit（限制结果数）

1、查询数值型数据:

SELECT * FROM tb_name WHERE sum > 100;

查询谓词:>,=,b51c1dff9f981b354aedd874329dcdbc,!=,!>,!a64e859162c6a580c74acfeb0b41c8ef,=7e8369ac7f9506ef16e439bdc191d741'' order by addtime desc

Note:a8093152e673feb7aba1828c43532094Equivalent to !=

in PHP 6. Use variables to query numerical data

SELECT * FROM tb_name WHERE id = '$_POST[text]'

Note: When using variables to query data, the variables passed into SQL do not need to be enclosed in quotation marks, because when strings in PHP are connected to numerical data, The program will automatically convert the numeric data into a string, and then connect it with the string to be connected

7. Use variables to query string data

SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%'

The exact matching method "%%" means it can appear anywhere

8. Query the first n records

SELECT * FROM tb_name LIMIT 0,$N;

The limit statement is used in conjunction with other statements, such as order by, etc., and the SQL statement will be used in an ever-changing manner, making the program very flexible

9. Query the next n records

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n

10. Query starts from the specified position n records

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n

Note: The id of the data starts from 0

11. Query the first n records in the statistical results

SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num

12. Query the data of the specified time period

SELECT The field to be found FROM table name WHERE field name BETWEEN initial value AND end value

SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

13. Query statistical data by month

SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;

Note: provided in SQL language The following functions can be used to easily query by year, month, and day

year(data): Return the value corresponding to the AD year and minute in the data expression

month (data): Returns the value corresponding to the month and minute in the data expression

day(data): Returns the value corresponding to the date in the data expression

14. Query Records greater than the specified condition

SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;

15. The query results do not display duplicate records

SELECT DISTINCT field name FROM table name WHERE query condition

Note: DISTINCT in the SQL statement must be used in conjunction with the WHERE clause, otherwise the output information will not change and the field cannot be used *Replace

16, NOT and predicate to query the combined conditions

(1)NOT BERWEEN … AND … for data between the starting value and the ending value The row query can be changed to de12f860197639ae3921070259e61bc0End value

(2)IS NOT NULL Query for non-null values

(3)IS NULL Query for null values

(4)NOT IN This formula specifies the search for an expression based on whether the keyword used is included in the list or excluded from the list. The search expression can be a constant or a column name, and the column name can be A set of constants, but in more cases it is a subquery

17. Display duplicate records and the number of records in the data table

SELECT name,age,count( *) ,age FROM tb_stu WHERE age = '19' group by date

18. Query data in descending/ascending order

SELECT field name FROM tb_stu WHERE condition ORDER BY field DESC descending order

SELECT field name FROM tb_stu WHERE condition ORDER BY field ASC ascending order

Note: If you do not specify a sorting method when sorting fields, the default is ASC ascending order

19. Perform multi-condition query on data

SELECT field name FROM tb_stu WHERE condition ORDER BY field 1 ASC field 2 DESC...

Note: Perform query information Multi-condition sorting is to jointly limit the output of records. Generally, since it is not restricted by a single condition, there are some differences in the output effect.

20. Sort statistical results

The function SUM([ALL] field name) or SUM([DISTINCT] field name) can realize the summation of fields. Function When it is ALL, it is the sum of all records in this field. If it is DISTINCT, it is the sum of all the fields of all non-duplicate records in this field.

For example: SELECT name, SUM(price) AS sumprice FROM tb_price GROUP BY name

SELECT * FROM tb_name ORDER BY mount DESC,price ASC

21. Single column data grouping statistics

SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC

Note: When the group by sorting statement order by appears in the SQL statement at the same time, the grouping statement must be written in front of the sorting statement, otherwise An error will occur

22. Multi-column data grouping statistics

Multiple-column data grouping statistics are similar to single-column data grouping statistics

SELECT *, SUM( Field 1*Field 2) AS (new field 1) FROM table name GROUP BY field ORDER BY new field 1 DESC

SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC

Note: The group by statement is generally followed by a sequence that is not an aggregate function, that is, it is not a column to be grouped

23, multi-table grouping statistics

SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;

The above is the content of MySQL, statement collection. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!