MySQL Data manipulation

Source: Internet
Author: User
Tags logical operators

Data add and delete change to insert data
1. 插入完整数据(顺序插入)    语法一:    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);    语法二:    INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定字段插入数据    语法:    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录    语法:    INSERT INTO 表名 VALUES        (值1,值2,值3…值n),        (值1,值2,值3…值n),        (值1,值2,值3…值n);4. 插入查询结果    语法:    INSERT INTO 表名(字段1,字段2,字段3…字段n)                     SELECT (字段1,字段2,字段3…字段n) FROM 表2                    WHERE …;
Update data
语法:    UPDATE 表名 SET        字段1=值1,        字段2=值2,        WHERE CONDITION;示例:    UPDATE student set sname=‘sb‘,sex=‘male‘ WHERE sid=2;
Delete data
语法:    DELETE FROM 表名         WHERE CONITION;示例:    DELETE FROM student WHERE sid=2;
Rights Management

#授权表user #该表放行的权限,针对:所有数据,所有库下所有表,以及表下的所有字段db #该表放行的权限,针对:某一数据库,该数据库下的所有表,以及表下的所有字段tables_priv #该表放行的权限。针对:某一张表,以及该表下的所有字段columns_priv #该表放行的权限,针对:某一个字段#按图解释:user:放行db1,db2及其包含的所有db:放行db1,及其db1包含的所有tables_priv:放行db1.table1,及其该表包含的所有columns_prive:放行db1.table1.column1,只放行该字段

code example

Create user ' Egon ' @ ' 1.1.1.1 ' identified by ' 123 '; Create user ' Egon ' @ ' 192.168.1.% ' identified by ' 123 '; Create user ' ego n ' @ '% ' identified by ' 123 '; #授权: to a folder, to a file, to the permissions of a field in a file to view Help: the common privileges of the helper grant are: Select,update,alter, DeleteAll can represent all permissions except grant # for authorization for all libraries: *.*grant Select On *. * to ' egon1 ' @ ' localhost ' identified by ' 123 '; #只在user表中可以查到egon1用户的select权限被设置为Y # for a database: Db1.*grant select on db1.* to ' egon2 ' @ '% ' identified by ' 123 ';  #只在db表中可以查到egon2用户的select权限被设置为Y # for a table: Db1.t1grant select on Db1.t1 to ' egon3 ' @ '% ' identified by ' 123 '; #只在tables_priv表中可以查到egon3用户的select权限 # for a field:mysql> select * from t3;+------+-------+------+| ID | name |    Age |+------+-------+------+| 1 |   Egon1 |    18 | | 2 |   Egon2 |    19 | | 3 |   Egon3 | |+------+-------+------+grant Select (id,name), update (age) on Db1.t3 to ' egon4 ' @ ' localhost ' identified by ' 123 '; #可以在tables_priv和columns_priv中看到相应的权限mysql > select * from Tables_priv where user= ' Egon4 ' \g************************ 1. Row *********************Host:localhost db:db1 user:egon4 table_name:t3 grantor: [email protected] Timestam p:0000-00-00 00:00:00 Table_priv:column_priv:select,updaterow in Set (0.00 sec) mysql> Select * from Columns_priv wher E user= ' Egon4 ' \g*************************** 1. Row *************************** host:localhost db:db1 user:egon4 Table_name:t3Column_name:id Tim estamp:0000-00-00 00:00:00column_priv:select*************************** 2. Row *************************** host:localhost db:db1 user:egon4 Table_name:t3Column_name:name T imestamp:0000-00-00 00:00:00column_priv:select*************************** 3. Row *************************** host:localhost db:db1 user:egon4 Table_name:t3Column_name:age Ti Mestamp:0000-00-00 00:00:00column_priv:updaterows in Set (0.00 sec) #删除权限revoke Select on db1.* from ' Egon ' @ '% ';
Single-Table Query
SELECT 字段1,字段2... FROM 表名                  WHERE 条件                  GROUP BY field                  HAVING 筛选                  ORDER BY field                  LIMIT 限制条数
The priority of a single-table query 1. Find table: From2. Take the constraints specified in the Where, go to the File/table to take out a strip of records 3. Group BY, if there is no group BY, The whole as a group of 4. The results of the grouping are filtered 5. Perform select6. Go to weight 7. Sort the results by condition: order By8. Limit the number of display bars for results simple query combat
Company.employee employee ID ID int name emp_name varchar sex s                Ex enum age int entry Date Hire_date Date post post              VarChar Job description post_comment varchar Salary Salary double office office INT department number depart_id int# creating table Create tables employee (ID int not NULL unique auto_increment,name varcha  R () not null,sex enum (' Male ', ' female ') is not null default ' male ', #大部分是男的age int (3) unsigned NOT NULL default 28,hire_date Date not null,post varchar (+), post_comment varchar (+), salary double (15,2), office int, #一个部门一个屋子depart_id int); View table Structure mysql> desc employee;+--------------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-----------------------+------+-----+---------+----------------+|  Id         | Int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) |     NO | |                NULL | || sex | Enum (' Male ', ' female ') |     NO | |                Male | || Age | Int (3) unsigned |     NO | |                28 | || hire_date | Date |     NO | |                NULL | || Post | varchar (50) |     YES | |                NULL | || post_comment | varchar (100) |     YES | |                NULL | || Salary | Double (15,2) |     YES | |                NULL | || Office | Int (11) |     YES | |                NULL | || depart_id | Int (11) |     YES | |                NULL | |+--------------+-----------------------+------+-----+---------+----------------+ #插入记录 # Three departments: teaching, Sales, operations insert INTO Employee (NAME,SEX,AGE,HIRE_DATE,POST,SALARY,OFFICE,DEPART_ID) VALUES (' Egon ', ' Male ', 18, ' 20170301', ' The old boy's diplomatic ambassador to the Shahe office ', 7300.33,401,1, #以下是教学部 (' Alex ', ' Male ', ' 20150302 ', ' teacher ', 1000000.31,401,1 '), (' Wupeiqi ', ' Male ', Bayi, ' 20130305 ', ' teacher ', 8300,401,1 ', (' Yuanhao ', ' Male ', ' the ' 20140701 ', ' the ', ' the ', ' the ', "Teacher ', 3500,401,1 '), (' Liwenzhou ', ' Male ', ' 20121101 ', ' teacher ', ' 2100,401,1 ', ' Jingliyang ', ' female ', ' 20110211 ', ' teacher ', 9000,401,1 ', (' jinxin ') , ' Male ', ' 19000301 ', ' teacher ', 30000,401, 1), (' Into the Dragon ', ' Male ', ', ' 20101111 ', ' teacher ', 10000,401, 1), (' Crooked ', ' female ', , ' 20150311 ', ' sale ', 3000.13,402,2), #以下是销售部门 (' Ya Ya ', ' female ', ' a ', ' 20101101 ', ' Sale ', 2000.35,402,2 '), (' Ding ', ' female ', (' 20110312 ', ' sale ', 1000.37,402,2), (' stars ', ' female ', ', ' 20160513 ', ' sale ', ' 3000.29,402,2 '), (' Princess ', ' female ', 28, ' 20170127 ', ' sale ', 4000.33,402,2), (' Ono ', ' Male ', ', ' 20160311 ', ' Operation ', 10000.13,403,3 '), #以下是运营部门 (' Cheng Bite gold ', ' male ', (' 19970312 ', ' Operation ', 20000,403, 3), (' Cheng Bites silver ', ' female ', ' 20130311 ', ' Operation ', 19000,403, 3), (' Cheng Bite copper ', ' male ', 18 , ' 20150411 ', ' Operation ', 18000,403, 3), (' Cheng Bite iron ', ' female ', ' 20140512 ', ' operation ', 17000,403,3); #ps: If you are in a Windows system, Insert Chinese characters, select'sThe result is blank, and all character encodings can be set uniformly to GBK 
Simple query
#简单查询    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id     FROM employee;    SELECT * FROM employee;    SELECT name,salary FROM employee;#避免重复DISTINCT    SELECT DISTINCT post FROM employee;    #通过四则运算查询    SELECT name, salary*12 FROM employee;    SELECT name, salary*12 AS Annual_salary FROM employee;    SELECT name, salary*12 Annual_salary FROM employee;#定义显示格式   CONCAT() 函数用于连接字符串   SELECT CONCAT(‘姓名: ‘,name,‘  年薪: ‘, salary*12)  AS Annual_salary    FROM employee;   CONCAT_WS() 第一个参数为分隔符   SELECT CONCAT_WS(‘:‘,name,salary*12)  AS Annual_salary    FROM employee;
Distinct, concat usage
select concat(‘<名字:‘,name,‘>    ‘,‘<薪资:‘,salary,‘>‘) from employee;select distinct depart_id from employee;select name,salary*12 annual_salary from employee;
WHERE usage

The WHERE clause can be used:

    1. comparison operator:> < >= <= <>! =
    2. between and 100 values between 10 and 20
    3. in (80,90,100) value is 10 or 20 or 3 0
    4. like ' egon% '
      pattern can be% or _,
      % means any number of characters
      _ represents a character
    5. logical operator: logical operators and or not
    6. can be used directly in multiple conditions
#1: Single-condition query SELECT name from employee WHERE post= ' sale '; #2: Multi-criteria Query SELECT name,salary from employee WHERE post= ' teacher ' and salary>10000; #3: keyword between and select n    Ame,salary from employee WHERE salary between 10000 and 20000;    SELECT Name,salary from the employee WHERE salary not between 10000 and 20000;    #4: The keyword is null (determines whether a field is null, cannot be used as an equals sign, need to be) SELECT name,post_comment from employee WHERE post_comment is null;            SELECT name,post_comment from employee WHERE post_comment are not NULL; SELECT name,post_comment from employee WHERE post_comment= ';        Note ' is an empty string, not a null PS: Perform update employee set post_comment= ' where id=2; And then check it out, there's going to be a result. # #: keyword in collection query SELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or S        alary=9000;    SELECT Name,salary from the employee WHERE salary in (3000,3500,4000,9000); SELECT name,salary from Employee WHERE salary not in (3000,3500,4000,9000); #6: keyword like fuzzy query wildcard '% ' SELECT * from employee WHERE name like ' eg    %‘; Wildcard ' _ ' SELECT * from the employee WHERE name like ' al__ ';
Little Practice
1. 查看岗位是teacher的员工姓名、年龄2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资4. 查看岗位描述不为NULL的员工信息5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
Answer
select name,age from employee where post = ‘teacher‘;select name,age from employee where post=‘teacher‘ and age > 30; select name,age,salary from employee where post=‘teacher‘ and salary between 9000 and 10000;select * from employee where post_comment is not null;select name,age,salary from employee where post=‘teacher‘ and salary in (10000,9000,30000);select name,age,salary from employee where post=‘teacher‘ and salary not in (10000,9000,30000);select name,salary*12 from employee where post=‘teacher‘ and name like ‘jin%‘;
Group query: GROUP BY
#1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的#2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等#3、为何要分组呢?    取每个部门的最高工资    取每个部门的员工数    取男人数和女人数小窍门:‘每’这个字后面的字段,就是我们分组的依据#4、大前提:    可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数
Only_full_group_by
#查看MySQL 5.7默认的sql_mode如下:mysql> select @@global.sql_mode;ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#!!!注意ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):mysql> set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
Mysql> SELECT @ @global. sql_mode;+-------------------+|                   @ @global. Sql_mode |+-------------------+| |+-------------------+1 Row in Set (0.00 sec) mysql> SELECT * from EMP GROUP by post; +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+---- -------+| ID | name | sex | Age | hire_date | Post | post_comment | Salary | Office | depart_id |+----+------+--------+-----+------------+----------------------------+--------------+------------+-- ------+-----------+| 14 | Koushik |  Male | 28 | 2016-03-11 | Operation |   NULL |    10000.13 |         403 |  3 | | 9 | Crooked |  Female | 48 | 2015-03-11 | Sale |    NULL |    3000.13 |         402 |  2 | | 2 | Alex |  Male | 78 | 2015-03-02 | Teacher | NULL |    1000000.31 |         401 |  1 | | 1 | Egon |  Male | 18 | 2017-03-01 | Old boy in Shahe office Diplomatic Ambassador |    NULL | 7300.33|         401 | 1 |+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-- ---------+4 rows in Set (0.00 sec) #由于没有设置ONLY_FULL_GROUP_BY, so you can have the result, the default is the first record in the group, but this is meaningless mysql> set global sql_ Mode= ' only_full_group_by '; Query OK, 0 rows Affected (0.00 sec) mysql> quit #设置成功后, be sure to exit and then re-login to take effect byemysql> use db1;database changedmysql> s Elect * from the EMP group by post; #报错ERROR 1055 (42000): ' db1.emp.id ' isn ' t in GROUP bymysql> select Post,count (ID) from the EMP GROUP by post; #只能查看分组依据和使用聚合函数 +----------------------------+-----------+| Post | Count (ID) |+----------------------------+-----------+|         Operation | 5 | |         Sale | 5 | |         Teacher | 7 | |         Old boy in Shahe office Diplomatic Ambassador | 1 |+----------------------------+-----------+4 rows in Set (0.00 sec)
GROUP by
单独使用GROUP BY关键字分组    SELECT post FROM employee GROUP BY post;    注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数GROUP BY关键字和GROUP_CONCAT()函数一起使用    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组,并查看组内成员名    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY与聚合函数一起使用    select post,count(id) as count from employee group by post;#按照岗位分组,并查看每个组有多少人
Emphasize
如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义多条记录之间的某个字段值相同,该字段通常用来作为分组的依据
Aggregation functions
#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组示例:    SELECT COUNT(*) FROM employee;    SELECT COUNT(*) FROM employee WHERE depart_id=1;    SELECT MAX(salary) FROM employee;    SELECT MIN(salary) FROM employee;    SELECT AVG(salary) FROM employee;    SELECT SUM(salary) FROM employee;    SELECT SUM(salary) FROM employee WHERE depart_id=3;
Having filter
#!!!执行优先级从高到低:where > group by > having #1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。#2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
Query sort: ORDER BY
按单列排序    SELECT * FROM employee ORDER BY salary;    SELECT * FROM employee ORDER BY salary ASC;    SELECT * FROM employee ORDER BY salary DESC;按多列排序:先按照age排序,如果年纪相同,则按照薪资排序    SELECT * from employee        ORDER BY age,        salary DESC;
Limit the number of records for a query: limit
示例:    SELECT * FROM employee ORDER BY salary DESC         LIMIT 3;                    #默认初始位置为0         SELECT * FROM employee ORDER BY salary DESC        LIMIT 0,5; #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条    SELECT * FROM employee ORDER BY salary DESC        LIMIT 5,5; #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
Querying using regular expressions

```
SELECT * FROM employee WHERE name REGEXP ' ^ale ';

SELECT * FROM employee WHERE name REGEXP ' on$ ';

SELECT * FROM employee WHERE name REGEXP ' m{2} ';

Summary: How strings are matched
WHERE name = ' Egon ';
WHERE name like ' yua% ';
WHERE name REGEXP ' on$ ';
``
Transferred from: http://www.cnblogs.com/linhaifeng/articles/7267592.html

Multi-Table Query

Https://www.luffycity.com/python-book/di-8-zhang-mysql-shu-ju-ku/84-shu-ju-cao-zuo/843-duo-biao-cha-xun.html

MySQL Data manipulation

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.