MySQL installation configuration
Installation and configuration of MySQL database
mysql5.5 mysql5.6 mysql5.7(stable) mysql8 Later version
Software download
Mysql5.7 address: https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
Special note
If installed Mysql Think again, or in the process of making a mistake sc delete mysql [Delete already installed mysql: careful]
Installation steps
Note: zip The installation file is a compressed file, and .exe The installation files are the same and should be strictly followed Perform the following steps
1. After downloading, you will get the zip installation file
2. It's best not to decompress the path in Chinese and spaces
3. Unzip it to E:\xxxMysql\mysql-5.7.19-winx64 directory [specify the directory according to your own situation]
4. Add environment variables: Computer - properties - advanced system settings - environment variables. Add mysql installation directory \ bin directory in Path environment, as shown in the following figure
5. In E: \ MySQL \ mysql5 7 directory to create my INI file, we need to create it directly
[client] # Client 3306 port port=3306 # Character set settings utf8 default-character-set=utf8 [mysqld] # mysqld service # Set as your MYSQL installation directory basedir=E:\mysql\mysql5.7\ # Set as MYSQL data directory, which is created by the system datadir=E:\mysql\mysql5.7\data\ port=3306 character_set_server=utf8 # Skip the security check and log in without a password skip-grant-tables
6. Open cmd as an administrator and switch to e: \ MySQL \ mysql5 7 \ bin directory, execute
mysqld -install Installation services
7. Initialize database:
mysqld --initialize-insecure --user=mysql If the execution is successful, a data catalogue
8. Start mysql service:
net start mysql
[stop mysql service instruction: net stop mysql]
9. Enter mysql management terminal:
mysql -u root -p [current root [user password is blank]
10. Modify root user password
user mysql; [Code usage database]
update user set authentication_string=password('123456') where user='root' and Host='localhost';
Interpretation: the above statement is to modify root The user password is 123456
Note: the command can be executed after the semicolon
Execution: flush privileges; Refresh permissions
sign out: quit
11. Modify my INI file, and the permission will be verified when you enter it again
# Skip the security check. After logging off, you need to enter the correct user name and password #skip-grant-tables
12. Rewrite and start mysql
net stop mysql net start mysql Tip: this command needs to exit mysql，stay Dos Next execution
13. Enter Mysql again and enter the correct user name and password
mysql -u root -p Enter the password to enter mysql
Use the command line window to connect to the MYSQL database
1,mysql -h host ip -P port -u user -p password (1)-p Password: no spaces (2)-p There is no password written in the back. Enter the password after entering (3)If not -h Host, the default is local (4)If not -P Port, 3306 by default (5)In practical work, the 3306 port number will generally be modified 2,Ensure that the service is started before logging in
start-up mysql Common database methods:[Dos command] 1,Service mode start(Interface) 2,net stop mysql service name 3,net start mysql service name
Navicat installation and use
Introduction: graphical MySQL management software
Download & install & use
Navicat address: http://www.navicat.com.cn/products
Navicat installation is very simple, basically a fool installation:
1. After downloading, you will get the exe installation file
2. Install as Administrator
3. Install in E:\xxx\Navicatxx directory [specify the directory according to your own situation]
4. Double click Run to configure the connection
5. Enter the correct password to log in to MySQL
SQLyog installation and use
Introduction: graphical MySQL management software
Download & install & use
SQLyog download address: https://sqlyog.en.softonic.com
SQLyog installation is very simple. It is basically a fool installation:
1. After downloading, you will get sqlyog-13.1.8-0 x64Trial. Exe installation file
2. Install as Administrator
3. Install in E:\SQLyog directory [specify the directory according to your own situation]
4. Double click Run to configure the connection
5. Enter the correct password to log in to mysql [ensure that the mysql service is running]
database
Three tier database structure - breaking the secret of MySQL
1. The so-called installation of Mysql database is to install a database management system (DBMS) on the host, which can manage multiple databases. DBMS(database manage system)
2. Multiple tables can be created in a database to hold data (information).
3. The relationship between database management system (DBMS), database and table is shown in the following figure:
Sketch Map:
Storage mode of data in database
SQL statement classification
- DDL: data definition statement [create table, library...]
- DML: data operation statement [add insert, modify update, delete]
- DQL: data query statement [select]
- DCL: data control statement [manage database: such as grant revoke]
Create database
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, carete_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name
1. CHARACTER SET: Specifies the CHARACTER SET used by the database. If you don't know the CHARACTER SET, it defaults to utf8
2. COLLATE: Specifies the collation rules of the database character set (commonly used utf8_bin [case sensitive], utf8_general_ci [case insensitive]. Note that the default is utf_general_ci)
Note: the character of the database is gbk, which is local my The INI file is configured as gbk
practice:
1. Create a file named ZZP_ Database of db01
2. Create a ZZP using the utf8 character set_ Db02 database
3. Create a ZZP using utf8 character set with proofreading rules_ Db03 database
# Demonstrate the operation of the database # Delete database directive drop database zzp_db01; # 1. Create a file named ZZP_ Database of db01 create database zzp_db01; # 2. Create a ZZP using the utf8 character set_ Db02 database create database zzp_db02 character set utf8; # 3. Create a ZZP using utf8 character set with proofreading rules_ Db03 database create database zzp_db03 character set utf8 collate utf8_bin;
Proofreading rules utf8_bin case sensitive default utf8_general_ci case insensitive presentation effect:
In zzp_db03 and zzp_db02 database creates t1 table, and the content data is as follows:
# Proofreading rules utf8_bin case sensitive default utf8_general_ci is not case sensitive # The following is a query sql, # select query * represents all fields from which table # where from which field name = 'tom' query name is tom select * from t1 where name = 'tom';
Character set utf8_bin
Character set utf8_general_ci
View and delete database
Show database statements: SHOW DATABASES Display database creation statement: SHOW CREATE DATABASE db_name; Database delete statement[Use with caution]: DROP DATABASE [IF EXISTS] db_name;
practice:
1. Query all databases in the current database server
2. Query the currently created ZZP_ Definition information of db01 database
3. Delete the previously created zzp_db01 database
# Show me how to delete and query a database # 1. Query all databases in the current database server show databases; # 2. Query the currently created ZZP_ Definition information of db01 database show create database zzp_db01; # Note: in order to avoid keywords when creating databases and tables, you can use backquotes #create database `create`; # 3. Delete the previously created zzp_db01 database drop database `create`; drop database `zzp_db01`;
Backup and restore database
Back up the database (Note: in DOS Execute) command line mysqldump -u user name -p -B Database 1 database 2 Database n > file name.sql[Full path] Restore database (Note: Enter Mysql Command line (executing) Source file name.sql
practice:
databases03.sql backup zzp_db02 and zzp_db03 and restore
# Exercise: databases03 SQL backup zzp_db02 and zzp_db03 and restore # For backup, execute the mysqldump command under Dos. In fact, it is in the mysql installation directory \ bin # The backup file is the corresponding sql statement mysqldump -u root -p -B zzp_db02 zzp_db03 > f:\\data\\bak.sql # Delete database drop database zzp_db02; drop database zzp_db03; # Recover the database (Note: enter the Mysql command line and execute it) source f:\\data\\bak.sql # The second method is to restore bak The content of SQL is put into navicat/SQLyog query compiler and executed
Backup and restore database tables
Table of backup Library
# backups mysqldump -u user name -p Password database table 1 Table 2 table n > d:\\file name.sql # recovery source d:\\file name.sql
practice:
# Backup table data of database mysqldump -u root -p zzp_db02 t1 > f:\\data\\t1.sql # Recover the table data of the database (note to enter zzp_db02 library first and execute the command) source f:\\data\\t1.sql
surface
Create table
CREATE TABLE table_name ( field1 datatype; field2 datatype; field3 datatype; )character set character set colllate Proofreading rules engine Storage engine; explain: field: Specify column name datatype: Specify column type (field type) character set: If not specified, it is the character set of the database colllate: If not specified, it is the proofreading rule of the database engine: Storage engine
be careful: zzp_db02 When creating a table, you should create corresponding columns according to the saved data and according to the type of data Define the corresponding column type. For example: user surface id plastic name character string password character string birthday date
# Instruction creation table create table if not exists `user`( `id` int, `name` varchar(255), `password` varchar(255), `birthday` date )character set utf8 collate utf8_bin engine innodb;
Mysql common data types (column types)
| classification | data type | explain |
|---|---|---|
| value type | BIT(M) | Bit type. M specifies the number of digits. The default value is 1 and the range is 1-64 |
| value type | TINYINT [UNSIGNED] takes 1 byte | Signed range - 128 to 127. Unsigned 0 to 255. The default is signed |
| value type | SMALLINT [UNSIGNED] takes 2 bytes | Signed negative 2 ^ 15 to 2 ^ 15-1, unsigned 0 to 2 ^ 16-1 |
| value type | MEDIUMINT [UNSIGNED] 3 bytes | Signed negative 2 ^ 23 to 2 ^ 23-1, unsigned 0 to 2 ^ 24-1 |
| value type | INT [UNSIGNED] 4 bytes | Signed negative 2 ^ 31 to 2 ^ 31-1, unsigned 0 to 2 ^ 32-1 |
| value type | BIGINT [UNSIGNED] 8 bytes | Signed negative 2 ^ 63 to 2 ^ 63-1, unsigned 0 to 2 ^ 64-1 |
| value type | ||
| value type | FLOAT [UNSIGNED] | 4 bytes of space |
| value type | DOUBLE [UNSIGNED] | Represents decimals with greater precision than float, occupying 8 bytes of space |
| value type | DECIMAL(M,D) [UNSIGNED] | Fixed point number M specifies the length, and D represents the number of decimal places |
| File, binary type | CHAR(size) char(20) | Fixed length string Max 255 |
| File, binary type | VARCHAR(size) char(20) | Variable length string 0 ~ 65535 [i.e. 2 ^ 16-1] |
| File, binary type | BLOB LONGBLOB | Binary data BLOB 0~2^16-1 LONGBLOB 0~2^32-1 |
| File, binary type | TEXT LONGTEXT | Texttext 0 ~ 2 ^ 16 longtext 0 ~ 2 ^ 32 |
| Time and date | DATE/DATETIME/TimeStamp | Date type (YYYY-MM-DD)(YYYY-MM-DD HH:MM:SS). TimeStamp represents TimeStamp, which is used to record inset and update operation time |
Basic use of numeric type (integer)
1. Description, usage specification: try to choose the type that takes up less space when it can meet the needs
| type | byte | minimum value | Maximum |
|---|---|---|---|
| (signed / unsigned) | (signed / unsigned) | ||
| TINYINT | 1 | -128 | 127 |
| 0 | 255 | ||
| SMALLINT | 2 | -32768 | 32767 |
| 0 | 65535 | ||
| MEDIUMINT | 3 | -8388608 | 8388607 |
| 0 | 16777215 | ||
| INT | 4 | -2147483648 | 2147483647 |
| 0 | 4294967295 | ||
| BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
| 0 | 18446744073709551615 |
2. Application examples
# zzp_ The db02 library uses tinyint to demonstrate that the range has symbols - 128 ~ 127 and no symbols 0-255 # Description: table character set, proofreading rules, storage engine, use the default create table `t2`( `id` TINYINT ); # 1. If unsigned is not specified, TINYINT is signed # This is a very simple addition statement insert into t2 values ( -129 ); # Error reporting exceeds the field range select * from t2;
3. How to define an unsigned integer
create table t10 (id tinyint);//The default is signed create table t11 (id tinyint unsigned);//Unsigned
# Show me how to create an unsigned table unsigned 0 - 255 create table `t3` (`id` tinyint unsigned); insert into t3 values (-1); # Error out of range insert into t3 values (256);# Error out of range select * from t3;
Use of bit
1. Basic use
mysql> create table t04 (num bit(8)); mysql> insert into t04 (1,3); mysql> insert into t04 values (2,65);
# Demonstrate bit type usage # 1. bit(m) m is in the range of 1 - 64 # 2. Add the data range and determine the number of bits you give. For example, m=8 represents a byte 0 ~ 255 # 3. Display according to bit # 4. When querying, you can still query by number create table t04 (num bit(8));# 0~255 insert into t04 values (1); insert into t04 values (3); insert into t04 values (255); insert into t04 values (256); # Error out of range select * from t04; select * from t04 where num =1;
2. Details
- When the bit field is displayed, it is displayed in bit mode
- You can still use the added value when querying
- If a value is only 0,1, bit(1) can be considered to save space
- Bit type. M specifies the number of digits, the default value is 1, and the range is 1 ~ 64
- Not much use
Basic use of numeric type (decimal)
1,FLOAT/DOUBLE [UNSIGNED]
Float single precision, Double double precision
2,DECIMAL[M,D] [UNSIGNED]
- M ore accurate small digits can be supported. The number of decimal places after (D) is the total number of decimal places
- If D is 0, the value has no decimal point or fractional part. M Max 65. D Max is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.
- Suggestion: if you want high precision of decimals, decimal is recommended
3. Case demonstration
# Demonstrate the use of decimal type, float and double # Create table CREATE TABLE t05( num1 FLOAT, num2 DOUBLE, num3 DECIMAL(30,20) ); # Add data INSERT INTO t05 VALUES(88.12345678912345,88.12345678912345,88.12345678912345); SELECT * FROM t05; # decimal can store large numbers CREATE TABLE t06(num DECIMAL(65)); INSERT INTO t06 VALUES(86456486465465464451648656168465156486651845316846116845168456); SELECT * FROM t06; CREATE TABLE t07(num BIGINT UNSIGNED); INSERT INTO t07 VALUES(86456486465465464451648656168465156486651845316846116845168456); # Error data out of BIGINT UNSIGNED range
Basic use of string
CHAR(size)
Fixed length string up to 255 characters
VARCHAR(szie)
Maximum 65535 bytes of variable length string [utf8 encoding maximum 65535 / 3 = 21844 characters, 1-3 bytes for recording size]
Application case:
# Demonstrates the use of char varchar for string types # Shortcut key for comment ctrl +/ -- CHAR(size) -- Fixed length string up to 255 characters -- VARCHAR(szie) -- Variable length string up to 65535 bytes[ utf8 Maximum code 65535/3=21844 Character 1-3 Bytes for record size] -- If the table code is utf8 varchar(size) size = (65535-3)/3 = 21844 -- If the table code is gbk varchar(size) size = (65535-3)/2 = 32766 create table t08 ( `name` char(255) # char type Max 255 ); create table t09 ( `name` varchar(21844) # varchar type Max 21845-1 );
String usage details
1. Detail 1
char(4) this 4 represents the number of characters (the maximum number of char is 255), not the number of bytes. Whether it is Chinese or letters, put four, calculated by characters
varchar(4) this 4 represents the number of characters. Whether it is alphabetic or Chinese, the data is stored in the code of the defined table.
Whether Chinese or English letters, they are stored up to 4, which are stored according to characters
# Demonstrates the use details of string types
# char(4) and varchar(4) represent characters, not bytes, and do not distinguish between Chinese characters or alphanumeric characters
create table t10 (`name` char(4));
insert into t10 values("zzp sir"); # Error out of 4 range
create table t11 (`name` varchar(4));
insert into t11 values("zzp sir"); # Error out of 4 range
2. Detail 2
char(4) is a fixed length (fixed size), that is, even if you insert "aa", it will occupy the allocated 4 characters;
varchar(4) is variable length (variable size), that is, if you insert "aa", the actual occupied space is not 4 characters, but allocated according to the actual occupied space (Note: varchar itself needs to occupy 1-3 bytes to record the length of the stored content)
L (actual data size) + (1 ~ 3) bytes
3. Detail 3
When to use char and when to use varchar
(1) if data is fixed, char is recommended, such as md5's password, zip code, mobile phone number, ID number, etc. char(32)
(2) If the length of a field is uncertain, we also have varchar, such as messages and articles
Query speed: char > varchar
4. Detail 4
When storing TEXT, you can also use the TEXT data type. You can treat the TEXT column as a VARCHAR column,
Note that Text cannot have a default value, with a size of 0-2 ^ 16 bytes
If you want to store more characters, you can choose
Mediamtext 0-2 ^ 24 or LONGTEXT 0-2^32
# If varchar is not enough, consider using medium text and long text
# If you want to be simple, you can use text directly
CREATE TABLE t12 (`content` TEXT,`content2` MEDIUMTEXT, `content3` LONGTEXT);
INSERT INTO t12 VALUES("zzp sir","zzp Sir 100","zzp Sir 100000~~");
SELECT * FROM t12;
Basic use of date type
CREATE TABLE birthday( t1 DATE, t2 DATETIME, t3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); timestamp: time stamp
mysql > INSERT INFO birthday (t1,t2) VALUES('2022-11-11','2022-11-11 10:10:10');
# Presentation time related types
# Create a table, date, datetime, timestamp
create table t13(
birthday date, -- birthday
jobtime datetime, -- Record month, day, hour, minute and second
login_time timestamp not null default current_timestamp on update current_timestamp -- Update time when login time is updated
);
insert into t13(birthday,jobtime) values('2022-11-11','2022-11-11 10:10:10');
-- If we update t13 A record of a day login_time The column will be automatically updated with the current time
select * from t13;
drop table t13
Details of date type
TimeStamp will be automatically updated during Insert and Update
Create table exercise
Create an employee table emp and select the appropriate data type
| field | attribute |
|---|---|
| id | plastic |
| name | character |
| sex | character |
| birthday | Date type (date) |
| entry_date | Date type (date) |
| job | character |
| Salary | Decimal type |
| resume | Text type |
# Create table exercise -- field attribute -- id plastic -- name character -- sex character -- birthday Date type( date) -- entry_date Date type( date) -- job character -- Salary Decimal type -- resume Text type create table `tmp`( `id` int, `name` varchar(32), `sex` char(1), `birthday` date, `entry_date` datetime, `job` varchar(32), `salary` double, `resume` TEXT )charset utf8 collate utf8_bin engine innodb; -- Add a piece of data insert into `tmp` values(100,'goblin ','male','2000-11-11','2010-11-11 11:11:11', 'Mountain patrolling',3000,'The king makes me petrol in the mountain'); select * from `tmp`;
modify
Use the ALTER TABLE statement to append, modify, or delete column syntax
Add column
ALTER TABLE table_name ADD (column datatype [DEFAULT expr]) [, column datatype] ...);
Modify column
ALTER TABLE table_name MODIFY (column datatype [DEFAULT expr]) [, column datatype] ...);
Delete column
ALTER TABLE table_name DROP (column); View the structure of the table: desc Table name;-- You can view the columns of the table
Modify table name: Rename table Table name to New table name Modify table character set: alter table Table name character set character set;
Application examples
- Add an image column on the emp of the employee table, varchar type (after resume is required)
- Modify the job column so that its length is 60
- Delete sex column
- The table name is employee
- Modify the character set of the table utf-8
- Modify the column name to user_name
-- Employee table emp Add one on image Column, varchar Type (required at resume (back) alter table `emp` add image varchar(32) not null default '' -- image The column is empty by default after `resume`; -- stay resume behind -- modify job Column so that its length is 60 alter table `emp` modify job varchar(60) not null default ''; -- delete sex column alter table `emp` drop sex; -- The table name is employee rename table `emp` to `employee`; -- Modify the character set of the table utf-8 alter table `employee` character set utf8; -- Listing name Change to user_name alter table `employee` change `name` `user_name` varchar(32) not null default ''; -- Displays the structure of the table,You can view all the columns of the table desc `employee`;
CRUD
Database C[create]R[read]U[update]D[delete] statement
1. Insert statement (add data)
2. Update statement (Update data)
3. Delete statement (delete data)
4. Select statement (find data)
Inset
Use the INSERT statement to INSERT data into a table
INSERT INTO table_name [(column [, column...])] VALUES (value [, value...])
Quick start cases:
1. Create a table of goods (id int, goods_name varchar(10), price double);
2. Add 2 records
# Practice insert statements -- 1,Create a product table goods(id int, goods_name varchar(10), price double); create table `goods`( `id` int, `goods_name` varchar(10), `price` double ); -- 2,Add 2 records insert into `goods` (id, goods_name, price) values (10,'iPhone',5000); insert into `goods` (id, goods_name, price) values (20,'Mi phones',2000); desc `goods`; select * from `goods`;
Insert details
1. The inserted data should be of the same data type as the field. For example, adding 'abc' to the int type will report an error
2. The length of the data should be within the fixed range of the column. For example, a string with a length of 80 cannot be added to a column with a length of 40
3. In values, the data position of the column must correspond to the arrangement position of the added column.
4. Character and date types should be enclosed in single quotes.
5. Null value can be inserted into the column [provided that the field can be null], insert into table value(null)
6,insert into table_name (column name..) Add polymorphic records in the form of values(), (), ()
7. If you are adding data to all fields in the table, you can not write the previous field name
8. The use of default value. When a field value is not given, if there is a default value, the default value will be added, otherwise an error will be reported
# Explain the details of the insert statement
-- 1,The inserted data should be of the same data type as the field. For example: put 'abc' Add to int The type will report an error
insert into `goods` (id, goods_name, price)
values ('abc','Huawei Mobile',3000); # Error 'abc' type mismatch
-- 2,The length of the data should be within the fixed range of the column. For example, a string with a length of 80 cannot be added to a column with a length of 40
insert into `goods` (id, goods_name, price)
values (40,'vovo mobile phone vovo mobile phone vovo mobile phone vovo mobile phone',2000); # Error Data too long for column 'goods_name'
-- 3,stay values The data position of the column in must correspond to the arrangement position of the added column.
insert into `goods` (id, goods_name, price)
values ('vovo mobile phone',40,3000); # Error: incorrect integer value: 'VO Mobile' for column 'id'
-- 4,Character and date types should be enclosed in single quotes.
insert into `goods` (id, goods_name, price)
values (40,vovo mobile phone,2000); # Error Unknown column 'vovo Mobile' in 'field list'
-- 5,Columns can be inserted with null values[Provided that this field can be null]，insert into table value(null)
insert into `goods` (id, goods_name, price)
values (40,'Huawei Mobile',null); # Column 'price' cannot be null
-- 6,insert into table_name (Listing..) values (),(),() Add polymorphic records as
insert into `goods` (id, goods_name, price)
values (50,'Samsung mobile phone',2300),(60,'Nokia Mobile',1800);
-- 7,If you are adding data to all fields in the table, you can not write the previous field name
insert into `goods` values (60,'Pineapple mobile phone',800);
-- 8,The use of default value. When a field value is not given, if there is a default value, the default value will be added, otherwise an error will be reported
-- If a column is not specified not null，If no value is given when adding data, it will be given by default null
-- If we want to specify the default value of a column, we can specify it when creating a table, such as: `price` duble not null default 100
insert into `goods` (id, goods_name)
values (60,'Gree mobile phone');
SELECT * from `goods`;
Update
Use the update statement to modify the data in the table
UPDATE table_name set col_name1 = expr1 [, col_name2 = expr2 ...] [ where where_definition]
Basic use
requirement:
Modify the records in the employee table created above
1. Change the salary of all employees to 5000 yuan
2. Change the salary of the staff of the name monster to 3000 yuan
3. Increase the salary of the old monster by 1000 yuan on the original basis
-- demonstration update sentence -- Created in employee Modifying records in a table -- 1,Change the salary of all employees to 5000 yuan.[If not where Conditions, will modify all records, be careful!] update employee set salary = 5000; -- 2,Change the salary of the staff of the name monster to 3000 yuan update employee set salary = 3000 where user_name = 'goblin '; -- 3,Increase the salary of the old monster by 1000 yuan on the original basis insert into `employee` values(200,'Old monster','1990-11-11','2000-11-11 10:10:10', 'Thumping',5000,'To beat the king's back','123.jpg'); update employee set salary = salary + 1000 where user_name = 'Old monster'; select * from employee;
Use details
1. The UPDATE syntax can UPDATE the columns of the original table row with new values
2. The SET clause indicates which columns to modify and which values to give
3. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows (records) will be updated, so use it with caution!
4. If you need to modify multiple fields, you can set field 1 = value 1, field 2 = value 2
Delete
Use delete statement to delete data in table
delete from table_name [where where_definition]
Quick start cases:
- Delete the record named 'old monster' in the table
- Delete all records in the table
-- delete Statement demonstration -- Delete table with name 'Old monster'Record of delete from `employee` where user_name = 'Old monster'; -- Delete all records in the table,Be careful! delete from `employee`;
Use details
1. If you do not use the where clause, all records in the table will be deleted
2. The Delete statement cannot Delete the value of a column (you can use update to set it to null or ')
3. Use the delete statement to delete only the records, not the table itself. If you delete a table, use the drop table statement. Drop table name;
Select
Basic grammar
SELECT [DISTINCT] *| {colnum1, colnum2, colnum3...}
FROM table_name
matters needing attention
1. Select specifies which columns of data to query
2. Column specifies the column name
3. * indicates to query all columns
4. From specifies which table to query
5. DISTINCT is optional. It refers to whether to remove duplicate data when displaying results
practice
1. Query the information of all students in the table
2. The names of all students in the query form and their corresponding English scores
3. Filter table duplicate data distinct
4. The records to be queried will be de duplicated only if each field is the same
# Create a new table (student) create table `student`( `id` int not null default 1, `name` varchar(20) not null default '', `chinese` float not null default 0.0, `english` float not null default 0.0, `math` float not null default 0.0 ); insert into `student`(id,name,chinese,english,math) values (1,'zzp sir',89,78,90); insert into `student`(id,name,chinese,english,math) values (2,'Fei Zhang',67,98,56); insert into `student`(id,name,chinese,english,math) values (3,'Song Jiang',87,78,77); insert into `student`(id,name,chinese,english,math) values (4,'Guan Yu',88,98,90); insert into `student`(id,name,chinese,english,math) values (5,'Zhao Yun',82,84,67); insert into `student`(id,name,chinese,english,math) values (6,'Ouyang Feng',55,85,45); insert into `student`(id,name,chinese,english,math) values (7,'Huang Rong',75,65,30); -- 1,Query the information of all students in the table select * from `student`; -- 2,The names of all students in the query form and their corresponding English scores select `name`,`english` from `student`; -- 3,Filter table duplicates distinct select distinct english from `student`; -- 4,The records to be queried will be de duplicated only if each field is the same select distinct `name`,english from `student`;
Use expressions to operate on the columns of the query
SELECT *| {column1 | expression, column2 | expression, ...}
FROM table_name
You can use the as statement in the select statement
SELECT column_name as alias from Table name;
practice
1. Count the total score of each student
2. Add 10 points to the total score of all students
3. Use alias to represent student scores
-- slect Statement use -- 1,Count the total score of each student select `name`, (chinese + english + math) from `student`; -- 2,Add 10 points to the total score of all students select `name`, (chinese + english + math + 10) from `student`; -- 3,Use alias to represent student scores select `name` as 'name', (chinese + english + math + 10) as total_score from `student`;
Operators often used in the where clause
| name | operator | explain |
|---|---|---|
| Comparison operator | > < <= >= = <>/!= | Greater than, less than, greater than (less than) equal to, not equal to |
| BETWEEN ...AND... | Values displayed in a range | |
| IN(set) | The value displayed in the int list, for example: in (100200) | |
| LIKE 'pattern' NOT LIKE '' | Fuzzy query | |
| IS NULL | Judge whether it is empty | |
| Logical operator | and | Multiple conditions hold at the same time |
| or | Any of several conditions holds | |
| not | Not established, for example: which not (salary > 100); |
Use the where clause to filter the query
1. Query the scores of students with the name of Zhao Yun
2. Query students with English scores greater than 90
3. Query the users whose total score is greater than 200
4. Query the scores of students whose math is greater than 60 and whose (and) id is greater than 4
5. Query students whose English scores are greater than Chinese scores
6. If the total score is more than 200 and the math score is less than the Chinese score, students surnamed Zhao
-- 1,Query the scores of students with the name of Zhao Yun select * from `student` where `name` = 'Zhao Yun'; -- 2,Query students with English scores greater than 90 select * from `student` where english > 90; -- 3,Query the users whose total score is greater than 200 select * from `student` where (chinese + english + math) > 200; -- 4,query math Greater than 60 and( and)id Student achievement greater than 4 select * from `student` where math > 60 and id > 4; -- 5,Query students whose English scores are greater than Chinese scores select * from `student` where english > chinese; -- 6,If the total score is more than 200 and the math score is less than the Chinese score, students surnamed Zhao -- Zhao% It means that the name starts with Zhao select * from `student` where (chinese + english + math) > 200 and math < chinese and `name` like 'Zhao%'; -- Query English score at 80-90 Students between select * from `student` where english >= 80 and english <= 90; -- Or use between..and... Is a closed interval select * from `student` where english between 80 and 90; -- Query math score at 89,90,91 My classmate select * from `student` where math = 89 or math = 90 or math = 91; -- Or use in select * from `student` where math in (89,90,91); -- Check the grades of all students surnamed Li select * from `student` where `name` like 'Zhao%'; -- Query mathematics>80，language>80 My classmate select * from `student` where math > 80 and chinese > 80; -- The query language score is 70-80 Students between select * from `student` where chinese between 70 and 80; -- Query students with a total score of 189190191 select * from `student` where (chinese + english + math) in (189,190,191); -- Check the scores of all students surnamed Li or song select * from `student` where `name` like 'Lee%' or `name` like 'Song Dynasty%'; -- Query students who have 30 points more in mathematics than Chinese select * from `student` where (math - chinese) > 30;
Sorting query results using the order by clause
SELECT column1, column2, column3, ... FROM table_name order by column asc|desc, ...
1. Order by specifies the column to be sorted. The sorted column can be either the column name in the table or the column name / alias specified after the select statement
2. Asc ascending [default], Desc descending
3. The ORDER BY clause should correspond to the end of the SELECT statement
practice:
- Sort the math scores and output [ascending]
- Sort the total score from top to bottom and output [descending]
- Sort and output the scores of students surnamed Li [ascending]
-- demonstration order by use -- Sort the math scores and output [ascending] select * from `student` order by math asc; -- Sort the total score from top to bottom and output [descending] -- Sort using alias select `name`, (chinese + english + math) as total_score from `student` order by total_score desc; -- Sort and output the scores of students surnamed Li [ascending] select * from `student` where `name` like 'Zhang%' order by math;
function
Statistical function
count
Count returns the total number of rows
Select count(*)|count(Listing) from table_name [where where_definition]
practice:
How many students are there in class one?
How many students have statistical math scores greater than 90?
How many people have a total score of more than 250?
Difference between count(*) and count (column)
-- demonstration mysql Use of statistical functions
-- Count the number of students in class one?
select count(*) from student;
-- How many students have statistical math scores greater than 90?
select count(*) from student where math >= 90;
-- How many people have a total score of more than 250?
select count(*) from student where (math + english + chinese) > 250;
-- count(*) and count(column) difference
-- Explanation: count(*) Returns the number of rows of records that meet the criteria
-- count(column): Count the number of columns that meet the conditions, but they will be excluded null Situation
create table `t14` (`name` varchar(20));
insert into t14 values ('tom');
insert into t14 values ('jack');
insert into t14 values ('mary');
insert into t14 values (null);
select count(*) from t14; -- 4
select count(`name`) from t14; -- 3
sum
The Sum function returns the Sum of rows that meet the where condition - generally used in numeric columns
Selete sum(Listing) {, sum(Listing) ...} from table_name
[where where_definition]
practice:
Count the total score of a class in mathematics?
Count the total scores of Chinese, English and mathematics in a class
Count the total score of a class in Chinese, English and mathematics
Count the average score of a class in Chinese
Note: sum only works on numerical values and has no meaning. Sum multiple columns and "," signs cannot be less.
# Demonstrate the use of sum function -- Count the total score of a class in mathematics? select sum(math) from student; -- Count the total scores of Chinese, English and mathematics in a class select sum(math) as 'mathematics',sum(english) as 'English',sum(chinese) as 'language' from student; -- Count the total score of a class in Chinese, English and mathematics select sum(math + english + chinese) from student; -- Count the average score of a class in Chinese select sum(chinese) / count(*) from student;
avg
The AVG function returns the average value of a column that satisfies the where condition
Select avg(Listing) {, avg(Listing) ...} from table_name
[where where_definition]
practice:
Find a class math average
Find the average of the total score of a class
# Demonstrate the use of avg function -- Find a class math average select avg(math) from student; -- Find the average of the total score of a class select avg(math + english + chinese) from student;
Max/min
The Max/min function returns the maximum / minimum value of a column that meets the where condition
Selete max(Listing) from table_name [where where_definition]
Exercise: get the highest and lowest scores of the class (the numerical range is particularly useful in Statistics)
# Demonstrate the use of the max/min function -- The class with the highest score and the lowest score select max(math + english + chinese),min(math + english + chinese) from student; -- Find the highest and lowest scores of mathematics in the class select max(math ),min(math) from student;
group by
Use the group by clause to group columns [create test table first]
SELECT column1, column2, column3... FROM table_name group by column
having
Use the having clause to filter the results after grouping
SELECT column1, column2, column3... FROM table_name group by column having ...
grop by is used for grouping statistics of j results of queries
The having clause is used to restrict the grouping of results
How to display the average wage and maximum wage of each department
Displays the average wage and minimum wage for each position in each department
Displays the department number whose average salary is less than 2000 and its average salary / / alias
# Department table create table `dept`( `deptno` mediumint unsigned not null default 0, `dname` varchar(20) not null default '', `loc` varchar(13) not null default '' ); insert into `dept` values(10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DELLAS'),(30,'SALES','CHCAGO'),(40,'OPERATIONS','BOSTON'); select * from dept; # Create EMP employee table create table `emp` ( `empno` mediumint unsigned not null default 0, /*number*/ `ename` varchar(20) not null default '',/*name*/ `job` varchar(9) not null default '',/*work*/ `mgr` mediumint unsigned, /*Superior number*/ `hiredate` date not null, /*Entry time*/ `sal` decimal(7,2) not null, /*Salary*/ `comm` decimal(7,2), /*Bonus*/ `deptno` mediumint unsigned not null default 0 /*Department number*/ ); -- Add test data insert into `emp` values(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20), (7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30), (7521,'WARD','SALESMAN',7698,'1991-2-22',1250.00,500.00,30), (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20), (7654,'MARTIN','SALESMAN',7698,'1991-9-28',1250.00,1400.00,30), (7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30), (7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10), (7788,'SCOTT','ANALYST',7566,'1997-4-19',3000.00,NULL,20), (7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10), (7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30), (7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30), (7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20), (7934,'MILLER','CLERK',7782,'1992-1-23',1300.00,NULL,10); select * from emp; # Employee level table create table `salgrade`( `grane` mediumint unsigned not null default 0,/*Wage scale*/ `losal` decimal(17.2) not null,/*Minimum wage at this level*/ `hisal` decimal(17.2) not null /*Maximum wage at this level*/ ); insert into salgrade values (1,700,1200); insert into salgrade values (2,1201,1400); insert into salgrade values (3,1401,2000); insert into salgrade values (4,2001,4000); insert into salgrade values (5,3001,9999); select * from salgrade;
-- demonstration group by + having -- How to display the average wage and maximum wage of each department -- analysis: avg(sal) max(sal) -- Group query by Department select avg(sal), max(sal), deptno from emp group by deptno; -- Displays the average wage and minimum wage for each position in each department -- Analysis: 1.Displays the average wage and minimum wage for each department -- 2.Displays the average wage and minimum wage for each position in each department select avg(sal), min(sal), deptno, job from emp group by deptno,job; -- Displays the department number whose average salary is less than 2000 and its average salary // alias -- analysis: -- 1. Displays the average salary and department number of each department select avg(sal), deptno from emp group by deptno; -- 2. On the basis of one result, filter and retain avg(sal) < 2000 select avg(sal), deptno from emp group by deptno having avg(sal) < 2000; -- 3. Use alias filtering select avg(sal) as avg_sal, deptno from emp group by deptno having avg_sal < 2000;
String function
| CHARSET(str) | Return string set |
|---|---|
| CONCAT(string2 [,...]) | Connection string |
| INSTR(string, substring ) | Returns the position where substring appears in the string, but does not return 0 |
| UCASE(string2) | Convert to uppercase |
| LCASE(string2) | Convert to lowercase |
| LEFT(string2, length)/RIGHT(string2, length) | Take length characters from the left / right in string2 |
| LENGTH(string) | string length [in bytes] |
| REPLACE(str, search_str, replace_str) | Replace in str_ STR replace search_str |
| STRCMP(string1, string2) | Compare two string sizes character by character |
| SUBSTRING(str, postion [, length]) | Start from the position of str [calculate from 1], and take length characters |
| LTRIM(string2 ) RTRIM(string2 ) trim | Remove front-end or back-end spaces |
-- Demonstrates the use of string related functions, using emp Table to demonstrate
-- CHARSET(str) Return string set
select charset(ename) from emp;
-- CONCAT(string2 [,...]) The connection string splices multiple columns into one column
select concat(ename,' Work is ',job) from emp;
-- INSTR(string, substring ) return substring stay string 0 is not returned for the position appearing in,
-- dual:The meta table system table can be used as a test table
select instr('zzping','ping') from DUAL;
-- UCASE(string2) Convert to uppercase
select ucase(ename) from emp;
-- LCASE(string2) Convert to lowercase
select lcase(ename) from emp;
-- LEFT(string2, length)/RIGHT(string2, length) from string2 Middle left/Right lifting length Characters
select left(ename, 2) from emp; -- 2 characters to the left
select right(ename, 2) from emp; -- 2 characters to the right
-- LENGTH(string) string length[Return length Chinese 1 according to bytes-3 byte]
select length(ename) from emp;
-- REPLACE(str, search_str, replace_str) stay str of use replace_str replace search_str
-- If it is manager Replace it with the manager
select ename, replace(job,'MANAGER','manager') from emp;
-- STRCMP(string1, string2) Compare two string sizes character by character
select strcmp('zzp','azp') from dual;
-- SUBSTRING(str, postion [, length]) from str of position Start [calculate from 1], and take length Characters
-- from ename Take out 2 characters from the first position of the column
select substring(ename, 1, 2) from emp;
-- LTRIM(string2 ) RTRIM(string2 ) trim Remove front-end or back-end spaces
select ltrim(' zzp') from dual;
select rtrim('zzp ') from dual;
select trim(' zzp ') from dual;
practice:
Displays the names of all employees emp in lowercase
-- Show all employees in lowercase emp Name of -- Method 1 -- Take out the idea first ename The first character of the character is converted to lowercase -- Splice it with the following string and output it select concat(lcase(substring(ename,1,1)), substring(ename,2)) as new_name from emp; -- Method 2 select concat(lcase(left(ename,1)), substring(ename,2)) as new_name from emp;
Mathematical function
| ABS(num) | absolute value |
|---|---|
| BIN(decimal_number) | Decimal to binary |
| CEILING(number2) | Round up to get the smallest integer larger than number2 |
| CONV(number2, from_base, to_base) | Binary conversion |
| FLOOR(number2) | Round down to get the largest integer smaller than number2 |
| FORMAT(number, decimal_places) | Keep decimal places (rounded) |
| HEX(DecimalNumber) | Convert to hex |
| LEAST(number, number2 [,...]) | Find the minimum value |
| MOD(numerator, denominator) | Seeking remainder |
| RAND([seed]) | RAND([seed]) returns a random number with a range of 0 < = V < = 1.0 |
Note: rand() returns a random floating-point value v, ranging from 0 to 1 (i.e. its range is 0 < = v < = 1.0). If an integer parameter N has been specified, it is used as a seed value to generate a repeat sequence.
-- Demonstrate mathematical correlation functions -- ABS(num) absolute value select abs(-10) from dual; -- BIN(decimal_number) Decimal to binary select bin(10) from dual; -- CEILING(number2) Round up to get the ratio number2 Large minimum integer select ceiling(1.1) from dual; -- 2 -- CONV(number2, from_base, to_base) Binary conversion -- The following meaning is that 8 is decimal 8, which is converted to binary output select conv(8, 10, 2) from dual; -- 1000 -- The following meaning is that 8 is hexadecimal 8, which is converted to binary output select conv(8, 16, 2) from dual; -- 8 -- FLOOR(number2) Round down to get the ratio number2 Small maximum integer select floor(1.1) from dual; -- 1 -- FORMAT(number, decimal_places) Keep decimal places(rounding) select format(78.123456, 4) from dual; -- 78.1235 -- HEX(DecimalNumber) Convert to hex select hex(11) from dual; -- B -- LEAST(number, number2 [,...]) Find the minimum value select least(0,1,-10,4) from dual; -- -10 -- MOD(numerator, denominator) Seeking remainder select mod(10, 3) from dual; -- 1 -- RAND([seed]) RAND([seed]) Returns a random number with a range of 0 <= v <= 1.0 -- Description: 1.If used rand() Return different random numbers each time, at 0 <= v <= 1.0 -- 2. If used rand(seed) Random number per time, At 0 <= v <= 1.0,If seed The random number does not change select rand(2) from dual;
Time and date
| CURRENT_DATE( ) | current date |
|---|---|
| CURRENT_TIME( ) | current time |
| CURRENT_TIMESTAMP( ) | Current timestamp |
| DATE(datetime) | Returns the date part of datetime |
| DATE_ADD(date2, INTERVAL d_value d_type) | Append the date or time to date2 |
| DATE_SUB(date2, INTERVAL d_value d_type) | Subtract a time from date2 |
| DATEDIFF(date1, date2) | Difference between two dates (the result is days) |
| TIMEDIFF(date1, date2) | Two time difference (hours, minutes, seconds) |
| NOW( ) | current time |
| YEAR|Month|DAY|DATE(datetime) FROM_UNIXTIME() unix_timestamp() | Time format conversion of month month day timestamp |
Details of the above functions:
1,DATE_ The interval in add() can be followed by year minute second day, etc
2,DATE_ interval in sub () can be followed by year minute second day, etc
3. DATEDIFF(date1, date2) gets the number of days, and it is the number of days from date1 to date2. Therefore, negative numbers can be taken
4. The date types of these four functions can be date, datetime or timestamp
-- Date time correlation function
-- CURRENT_DATE( ) current date
select current_date() from dual;
-- CURRENT_TIME( ) current time
select current_time() from dual;
-- CURRENT_TIMESTAMP( ) Current timestamp
select current_timestamp() from dual;
# Create test table information table
create table mes(id int, content varchar(30), send_time datetime);
# Add record
insert into mes values(1,'Journalism',current_timestamp());
insert into mes values(2,'News 2',now());
insert into mes values(4,'News 3','2021-11-11 11:11:11');
select * from mes;
# Application examples
# Display and use news information. The release date only displays the date, not the time
select id, content, date(send_time) from mes;
# Please check the posts posted in 10 minutes
-- DATE_ADD(date2, INTERVAL d_value d_type) stay date2 Add a date or time to the
-- DATE_SUB(date2, INTERVAL d_value d_type) stay date2 Subtract a time from the
select * from mes where date_add(send_time, interval 10 minute) >= now();
select * from mes where date_sub(now(), interval 10 minute) <= send_time;
# Please find the difference between 2011-11-11 and 1990-1-1 in the sql statement of mysql. DATEDIFF(date1, date2) Difference between two dates (the result is days)
select datediff('2011-11-11', '1990-01-01') from dual;
# Please find out how many days you have lived in the sql statement of mysql? For example, born on November 11, 1986
select datediff(now(), '1986-11-11') from dual;
# How long can you live for 80 days? For example, born on November 11, 1986
-- Ask for the date when you live until you are 80 x
-- Then in use datediff(x,now()): '1986-11-11' -> date
-- interval 80 year: year It can be month, day, hour, minute and second
-- '1986-11-11' Can be date，datetime，timestamp
select datediff(date_add('1986-11-11', interval 80 year), now()) from dual;
-- DATE(datetime) return datetime Date part of
select date(current_timestamp()) from dual;
select date(now()) from dual;
select date('2021-11-11 11:11:11') from dual;
-- TIMEDIFF(date1, date2) Two time difference(How many hours, how many minutes, how many seconds)
select timediff('10:11:11','06:10:10') from dual; -- 04:01:01
-- NOW( ) current time
select now() from dual;
-- YEAR|Month|DAY|DATE(datetime)
select year(now()) from dual;
select month(now()) from dual;
select day(now()) from dual;
select year('2013-10-10') from dual;
-- unix_timestamp() The return is 1970-1-1 Seconds to now
select unix_timestamp() from dual;
-- FROM_UNIXTIME() : You can put one unix_timestamp Seconds[time stamp]，A date converted to a specified format
-- %Y-%m-%d The format is specified, which indicates month, year and day
-- Meaning: in development, you can store an integer and then represent the time through FROM_UNIXTIME transformation
--
select from_unixtime(1618483484, '%Y-%m-%d') from dual;
select from_unixtime(1618483484, '%Y-%m-%d %H:%i:%s') from dual;
In actual development, we often use int to save a unix timestamp, and then use from_unixtime() is of great practical value
Encryption and system functions
| USER() | Query user |
|---|---|
| DATABASE() | Database name |
| MD5(str) | Calculate an MD5 32 string for the string and encrypt it (user password) |
| PASSWORD(str) select * from mysql.user \G | Calculate and return the password string from the original password str, which is usually used to encrypt the user password of mysql database |
Basic usage:
-- Demonstrate encryption functions and system functions
-- USER() Query user
-- Can view login to mysql Who are the users and who are logged in IP
select user() from dual; -- user@IP address
-- DATABASE() Query the currently used database name
select database() from dual;
select database();
-- MD5(str) Calculate a value for the string MD5 32 String,(User password)encryption
-- root Password encryption 123456 -> encryption md5 -> The encrypted password is stored in the database
select md5('123456') from dual;
select length(md5('123456')) from dual;
-- Demonstrate the user table. When storing the password, yes md5
create table users(
`id` int,
`name` varchar(20) not null default '',
pwd char(32) not null default ''
);
insert into users values(100,'zzp',md5('zzp'));
select * from users;
-- query
select * from users where `name`='zzp' and pwd=md5('zzp');
-- PASSWORD(str) -- Encryption function,stay MySQL The user password of the database is to use PASSWORD Function encryption
select password('123456') from dual; -- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
-- select * from mysql.user \G From original password str Calculates and returns a password string, usually used to mysql User password for database
-- Usually used for mysql User password encryption of database
-- mysql.user Represents the database.surface
select * from mysql.user;
Process control function
Let's look at two requirements first:
1. Query the emp table. If comm is null, 0.0 will be displayed
2. If the emp table is CLERK, the employee is displayed; if it is MANAGE, the manager is displayed; if it is SALESMAN, the salesperson is displayed; others are displayed normally
| IF(expr1, expr2, expr3) | If expr1 is true, expr2 is returned; otherwise, expr3 is returned |
|---|---|
| IFNULL(expr1, expr2) | If expr1 is not NULL, expr1 is returned, otherwise expr2 is returned |
| SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [similar to multiple branches] | If expr1 is true, expr2 is returned; if expr2 is true, expr4 is returned; otherwise, expr5 is returned |
# Demonstrate process control statements -- IF(expr1, expr2, expr3) If expr1 by true，Then return expr2 Otherwise return expr3 select if(true, 'Beijing','Shenzhen') from dual; -- IFNULL(expr1, expr2) If expr1 Not for NULL，Then return expr1，Otherwise return expr2 select ifnull(null,'zzp') from dual; -- SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;[Similar to multiple branches] -- If expr1 by true，Then return expr2，If expr2 by true，return expr4，Otherwise return expr5 select case when true then 'jack' -- jack when false then 'tom' else 'lili' end -- 1,query emp Table, if comm yes null，0 is displayed.0 -- Note: judge whether it is null To use is null，Judged not to be null To use is not null select ename,if(comm is null, 0.0, comm) from emp; select ename,ifnull(comm, 0.0) from emp; -- 2,If emp Table is CLERK The employee is displayed. If yes MANAGE The manager is displayed, -- If it is SALESMAN The salesperson will be displayed, and other normal displays will be displayed select ename, (select case when job = 'CLERK' then 'staff member' when job = 'MANAGER' then 'manager' when job = 'SALESMAN' then 'salesman' else job end) as 'job' , job from emp;
mysql query
strengthen
Introduction:
We talked about the basic query of mysql table earlier, but it is all about querying a table, which is far from enough in the actual software development.
In the following process, we will create three tables before use
(emp,dept,salgrade) demonstrates how to query multiple tables
use where clause ？How to find 1992.1.1 Employees who joined the company later How to use like Operator %: Represents 0 to more than one arbitrary character _: Represents a single arbitrary character ？How to display initials S Name and salary of the employee ？How to display the third character as uppercase O Name and salary of all employees How to display an employee without a superior Query table structure use order by clause ？How to display employee information in the order of salary from low to high ？Employee information is displayed in ascending order of department number and descending order of employee's salary
-- Strengthen query -- use where clause -- ？How to find 1992.1.1 Employees who joined the company later -- Description: in mysql In, date types can be directly compared. You need to pay attention to the format select * from emp where hiredate > '1992-01-01'; -- How to use like Operator (fuzzy query) -- %: Represents 0 to more than one arbitrary character _: Represents a single arbitrary character -- ？How to display initials S Name and salary of the employee select ename, sal from emp where ename like 'S%'; -- ？How to display the third character as uppercase O Name and salary of all employees select ename, sal from emp where ename like '__O%'; -- How to display an employee without a superior select * from emp where mgr is null; -- Query table structure desc emp; show create table emp; -- use order by clause -- ？How to display employee information in the order of salary from low to high select * from emp order by sal asc; -- ？Employee information is displayed in ascending order of department number and descending order of employee's salary select * from emp order by deptno asc, sal desc;
Paging query
1. Take it out in ascending order according to the employee's id number. Three records are displayed on each page. Please display page 1, page 2 and page 3 respectively
2. Basic syntax: Select Limit, start, rows: indicates to start from the start+1 line, take out the rows, and start is calculated from 0
3. Formula: select Limit number of records per page * (page number - 1), number of records per page
practice:
The id of each employee record is displayed in descending order of 5. Please display the corresponding sql statements on page 3 and page 5 respectively
-- Paging query -- By employee id Take out the number in ascending order, and 3 records are displayed on each page. Please display page 1, page 2 and page 3 respectively -- Page 1 select * from emp order by empno limit 0, 3; -- Page 2 select * from emp order by empno limit 3, 3; -- Page 3 select * from emp order by empno limit 6, 3; -- Deduce a formula select * from emp order by empno limit Number of records per page * (Page number -1), Number of records per page -- By employee id The number is taken out in descending order, and 5 records are displayed on each page. Please display the corresponding on page 3 and page 5 respectively sql sentence -- Page 3 select * from emp order by empno desc limit 10, 5; -- Page 5 select * from emp order by empno desc limit 20, 5;
Using grouping functions and grouping clauses
group by
(1) Displays the total number of employees and average salary of each position
(2) Displays the total number of employees and the number of employees receiving benefits
(3) Displays the total number of managers
(4) Displays the maximum difference in an employee's salary
-- enhance group by Use of -- (1)Displays the total number of employees and average salary of each position select count(*), avg(sal), job from emp group by job; -- (2)Displays the total number of employees and the number of employees receiving benefits -- Idea: the number of employees receiving subsidies is comm List as non null,namely comm(column)，If the value of this column is null，It won't be counted select count(*), count(comm) from emp; -- Extension: count the number of employees who do not receive subsidies select count(*), count(if(comm is null, 1, null)) from emp; select count(*), count(*) - count(comm) from emp; -- (3)Displays the total number of managers select count(distinct mgr) from emp; -- (4)Displays the maximum difference in an employee's salary -- Idea: max(sal) - min(sal) select max(sal) - min(sal) from emp;
Summary of data grouping
If the select statement also contains group by, having, limit and order by, their order is group by, having, order by, limit
Application case:
Please count the average salary of each department, which is greater than 1000, and sort it from the highest to the lowest according to the average salary, and take out the first two lines of records
SELECT column1, column2, column3 ... FROM table_name group by column having condition order by column limit start, rows;
-- Please count each department group by The average wage, avg -- And is greater than 1000 having，And ranked from the highest to the lowest according to the average wage order by， -- Take out the first two lines of records limit select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 1000 order by avg_sal desc limit 0, 2;
mysql multi table query
Cartesian set
select * from emp, dept;
There are 13 records in emp form
The dept table has four records
By default, when two tables are queried, the rules are as follows:
1. From the first table, take out a row and combine each row of the second table to return the result [including all columns of the two tables]
2. Total number of records returned: rows of the first table * rows of the second table = 13 * 4 = 52
3. The result returned from the default processing of such multi table queries is called Cartesian set
4. The key to solving this table is to write the correct filter addition conditions, where programmers need to analyze
explain
Multi table query refers to the query based on two or more tables. In practical application, querying a single table may not meet your needs. (practice with dept table and emp table)
Multi table query exercise
？Display employee name, employee salary and department name [Cartesian set] Tip: the condition of multi table query cannot be less than the number of tables-1，Otherwise, Cartesian sets appear ？How to display the Department name, employee name and salary with department number 10 ？Display the name, salary and salary level of each employee
Exercise: display the employee name, employee salary and the name of the Department, and sort by Department [descending]
-- multi-table query -- ？Display employee name, employee salary and department name [Cartesian set] # Analysis: 1 Employee name, employee salary from emp table # 2. The name of the department comes from the dept table # 3. You need to query emp and dept tables # 4. When we need to specify the column name of a table to display, we need a table Listing select ename, sal, dname, emp.deptno from emp, dept where emp.deptno = dept.deptno; -- Tip: the condition of multi table query cannot be less than the number of tables-1，Otherwise, Cartesian sets appear -- ？How to display the Department name, employee name and salary with department number 10 select ename, sal, dname, emp.deptno from emp, dept where emp.deptno = dept.deptno and emp.deptno = 10; -- ？Display the name, salary and salary level of each employee # Idea: name and salary are from 13 records in emp form # The salary level comes from 5 records in the salgrade table select ename, sal, grane from emp, salgrade where sal between losal and hisal; -- Display the employee name, employee salary and the name of the Department, and sort by department[Descending order] # Idea: employee name, employee salary from emp table # The name of the department comes from the dept table select ename, sal, dname, emp.deptno from emp, dept where emp.deptno = dept.deptno order by emp.deptno desc;
Self connection
Self join refers to the join query of the same table [treat the same table as two tables].
Thinking question: display the name of the company employee and the name of his superior
-- Self join of multi table query -- Thinking question: display the name of the company employee and the name of his superior # Analysis: the employee's name is in the emp table, and the superior's name is in the emp table # Employees and superiors are associated through the mgr column of the emp table select woker.ename as 'staff member', boss.ename as 'Superior name' from emp woker, emp boss where woker.mgr = boss.empno;
Summary:
Features of self connection: 1. Use the same table as two tables; 2. You need to give the table alias [table name table alias]; 3. If the column name is ambiguous, you can specify the alias of the column [column name as the alias of the column]
Subquery
What is a subquery
Subquery refers to the select statement embedded in other sql statements, also known as nested query
single-row subqueries
Single row subquery refers to a subquery statement that returns only one row of data
Think: how to display all employees in the same department as SMITH?
Multiline subquery
Multi row sub query refers to the sub query that returns multi row data, using the keyword in
practice;
How to query the name, position, salary and model department number of employees who work in the same department as department 10, but do not include their own employees in department 10.
-- Subquery -- Thinking: how to show and SMITH All employees in the same department? # Analysis: 1 First query the department number of SMITH select deptno from emp where ename = 'SMITH'; # 2. Use the above select statement as a subquery -- Here is the answer select * from emp where deptno = (select deptno from emp where ename = 'SMITH'); -- practice; -- How to query the names of employees who work the same as department 10 -- Position, salary and door number of the mold department, but excluding the employees of the No. 10 department. # Analysis: 1 What is the work of the inquiry 10 department select distinct job from emp where deptno = 10; # 2. Use the above select statement as a subquery -- The following statement is complete select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <> 10;
Sub query temporary table
Subqueries are used as temporary tables
Exercise: query the items with the highest price in each category in ecshop
Tip: sub query can be used as a temporary table
-- query ecshop The highest priced commodity in each category in the -- Query commodity table select goods_id, cat_id, goods_name, shop_price from ecs_goods; -- Get the highest price goods in each category first max + group by cat_id， As a temporary table -- Taking subquery as a temporary table can solve many complex queries select cat_id, max(shop_price) from ecs_goods group by cat_id; -- The following is the complete statement select goods_id, ecs_goods.cat_id, goods_name, shop_price from (select cat_id, max(shop_price) as max_price from ecs_goods group by cat_id) temp, ecs_goods where temp.cat_id = ecs_goods.cat_id and temp.max_price = ecs_goods.shop_price;
all and any
Using the all operator in a multiline subquery
Use the any operator in a multiline query
Think about: how to display the names, wages and department numbers of all employees whose wages are less than department 30 and those with higher wages
Think: how to display the name, salary and department number of an employee whose salary is higher than that of one of the employees in department 30
-- all and any Use of -- Think about: how to display the names, wages and department numbers of all employees whose wages are less than department 30 and those with higher wages select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30); -- perhaps select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30); -- Think: how to display the name, salary and department number of an employee whose salary is higher than that of one of the employees in department 30 select ename, sal, deptno from emp where sal > any(select sal from emp where deptno = 30); -- perhaps select ename, sal, deptno from emp where sal > (select min(sal) from emp where deptno = 30);
Multiple-column subqueries
Multi column sub query refers to the sub query statement that returns multiple column data
Exercise: please check the students whose grades are exactly the same as those of Song Jiang in mathematics, English, Chinese and so on
Thinking: how to query employees who are exactly the same as smith's department and position (and not including allen himself)
(field 1, field 2,...)= (select field 1, field 2 from...)
-- Multiple-column subqueries -- Thinking: how to query and allen Employees with exactly the same department and position (and excluding smith (I) -- (Field 1, Field 2,...) = (select Field 1, Field 2 from ...) # Analysis: 1. Departments and positions that get smith select deptno, job from emp where ename = 'ALLEN'; # 2. The above query is used as a subquery, and the syntax of multi column subquery is used for matching select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'ALLEN') and ename != 'ALLEN'; -- Exercise: please check the students whose grades are exactly the same as those of Song Jiang in mathematics, English, Chinese and so on select * from student where (math, english, chinese) = ( select math, english, chinese from student where `name` = 'Song Jiang');
Subquery exercise
Use subqueries in the from clause
Think: check the information of people whose salary in each department is higher than the average salary in this department
Here we need to use the query tips to use a sub query as a temporary table
Think: find out the details of the highest paid people in each department
Query the information of each department (including department name, number and address) and the number of personnel
Idea:
1. Display personnel information and department information first
2. Then statistics
-- Subquery exercise -- Think: check the information of people whose salary in each department is higher than the average salary in this department # 1. Get the department number of each department and the corresponding average salary first select deptno, avg(sal) as avg_sal from emp group by deptno; # 2. Take the above results as sub queries and perform multi table queries with emp select emp.deptno, ename, sal, avg_sal from emp, (select deptno, avg(sal) as avg_sal from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal > temp.avg_sal; -- Think: find out the details of the highest paid people in each department select emp.deptno, ename, sal, max_sal from emp, (select deptno, max(sal) as max_sal from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal = temp.max_sal; -- Query the information of each department (including department name, number and address) and the number of personnel -- Idea: -- 1,Display personnel information and department information first -- 2,Then statistics # 1. Department name, number and address are from dept table # 2. Number of personnel in each department = "build a temporary table select count(*), deptno from emp group by deptno; -- Here is the complete statement select dname, dept.deptno, loc, per_num as 'Number of people' from dept, (select count(*) as per_num, deptno from emp group by deptno) temp where dept.deptno = temp.deptno; -- Or there is another way to write a table.* Indicates that all columns of the table are displayed -- In multi table query, column names can be written directly only when the columns of multiple tables are not duplicate select temp.*, dname, loc from dept, (select count(*) as per_num, deptno from emp group by deptno) temp where dept.deptno = temp.deptno;
Table replication
Self replicating data (worm replication)
Sometimes, in order to test the efficiency of an sql statement, when we need a large amount of data, we can use this method to create data for the table
Think: then delete a table duplicate record
-- Replication of tables -- For someone sql Statement for efficiency test. When we need massive data, we can use this method to create data for the table create table my_tab01( id int, `name` varchar(32), sal double, job varchar(32), deptno int); desc my_tab01; select * from my_tab01; -- Show me how to copy myself -- 1. First emp Record copy of table my_tab01 insert into my_tab01(id, `name`, sal, job, deptno) select empno, ename, sal, job, deptno from emp; -- 2. Self replication insert into my_tab01 select * from my_tab01; select count(*) from my_tab01; -- Think: then delete a table duplicate record -- 1. Create a table first my_tab02 -- 2. Give Way my_tab02 There are duplicate records create table my_tab02 like emp; -- This statement puts emp Table structure(column)，Copy to my_tab02 desc my_tab02; -- Add data insert into my_tab02 select * from emp; select * from my_tab02; -- 3. Consider weight removal my_tab02 Record of # Idea: (1) first create a temporary table_ Temp, the structure of the table and my_ Same as tab02 create table my_temp like my_tab02; # (2) Put my_ The records of temp are copied to my after being processed by the distinct keyword_ temp insert into my_temp select distinct * from my_tab02; # (3) Clear out my_tab02 all records delete from my_tab02; # (4) Put my_ Copy temp table records to my_tab02 insert into my_tab02 select distinct * from my_temp; # (5) drop drop temporary table_ temp drop table my_temp;
Merge query
introduce
Sometimes in practical application, in order to combine the results of multiple select statements, set operators can be used
union,union all
1,union all
This operator is used to obtain the union of two result sets. When this operator is used, duplicate lines are not cancelled.
select ename, sal, job from emp where sal > 2500 union all select ename, sal, job from emp where job = 'MANAGER';
2,union
This operator is similar to union all, but automatically removes duplicate rows from the result set
select ename, sal, job from emp where sal > 2500 union select ename, sal, job from emp where job = 'MANAGER';
-- Merge query -- union all This is to merge the two query results without duplication select ename, sal, job from emp where sal > 2500 union all select ename, sal, job from emp where job = 'MANAGER'; -- union Merging the two query results will eliminate duplication select ename, sal, job from emp where sal > 2500 union select ename, sal, job from emp where job = 'MANAGER';
mysql off table connection
Ask a question
1. The query we learned earlier is to use the where clause to filter the Cartesian product formed by two or more tables. According to the association conditions, all matching records are displayed. Those that do not match are not displayed
2. For example: List Department names and the names and jobs of employees in these departments, and show which departments have no employees
3. Use the SQL of multi table query we have learned to see the effect? -- > External connection
-- External connection -- For example: list the names of departments and the names and jobs of employees in these departments, -- At the same time, it is required to show which departments have no employees -- Using the multi table query we have learned SQL，See how it works? select dname, ename, job from emp, dept where emp.deptno = dept.deptno order by dname; -- One department is missing
External connection
1,LEFT JOIN .. ON .. Left outer connection (if the table on the left shows completely, we mean left connection)
2,RIGHT JOIN .. ON .. Right outer connection (if the table on the right shows completely, we mean left connection)
3. Use the left outer connection (show everyone's score, if there is no score, also show the person's name and id number)
select ... from table 1 left join table 2 on condition [table 1: left table, table 2: right table]
4. Use the right external connection (display all scores, if there is no name match, display empty)
select ... from table 1 right join table 2 on condition [table 1: left table, table 2: right table]
-- External connection -- For example: list the names of departments and the names and jobs of employees in these departments, -- At the same time, it is required to show which departments have no employees -- Using the multi table query we have learned SQL，See how it works? select dname, ename, job from emp, dept where emp.deptno = dept.deptno order by dname; -- One department is missing -- establish stu create table stu(id int,`name` varchar(32)); insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono'); select * from stu; -- establish exam create table exam(id int,grade int); insert into exam values(1,56),(2,76),(11,8); select * from exam; -- Use left connection -- (Show everyone's grades. If there are no grades, the person's name and address should also be displayed id No.) select `name`, stu.id, grade from stu, exam where stu.id = exam.id; -- Change to left outer connection select `name`, stu.id, grade from stu left join exam on stu.id = exam.id; -- Right outer connection -- (Display all scores, if there is no name matching, the display is blank) -- That is, the table on the right( exam)If there is no matching record with the left table, the record of the right table will also be displayed select `name`, exam.id, grade from stu right join exam on stu.id = exam.id;
practice
List Department names and employee information (name and job) of these departments, as well as those departments without employees
1. Using left outer connection
2. Using right outer connection
-- List Department names and employee information for these departments(Name and job)，Also list which departments have no employees -- 1,Using left outer connection select dname, dept.deptno, ename, job from dept left join emp on dept.deptno = emp.deptno; -- 2,Using right outer connection select dname, dept.deptno, ename, job from emp right join dept on dept.deptno = emp.deptno;
Summary: in the actual development, we mostly use the previously learned connection [query two or more tables together]
mysql constraint
Basic introduction
Constraints are used to ensure that the database meets specific business rules.
In mysql, constraints include: not null, unique, primary key, foregin key and check
Primary key
Field name field type primary key
The data used to uniquely identify the table row. When the primary key constraint is defined, the column cannot be duplicate
-- Primary key usage -- id name email create table t16( `id` int primary key, -- express id Column is a primary key column `name` varchar(32), email varchar(32) ); -- The value of the primary key column cannot be repeated insert into t16 values(1,'jack','jack@sohu.com'); insert into t16 values(2,'tom','tom@sohu.com'); insert into t16 values(1,'zzp','zzp@sohu.com'); -- error Duplicate entry '1' for key 'PRIMARY' select * from t16;
Primary key - details
1. The primary key cannot be repeated and cannot be null
2. A table can only have one primary key at most, but it can be a composite primary key
3. There are two ways to specify a primary key
- Specify directly after the field name: field name primary kek
- Write the primary key (column name) at the end of the table definition
4. Using the desc table name, you can see the primary key
5. Reminder: in actual development, a primary key is often designed for each table.
-- primary key(Primary key) - Details -- 1,primary key Cannot be repeated and cannot be null insert into t16 values(null,'zzp','zzp@sohu.com'); -- error Column 'id' cannot be null -- 2,A table can only have one primary key at most, but it can be a composite primary key create table t18( `id` int primary key, -- express id Column is a primary key column `name` varchar(32) primary key, -- error Multiple primary key defined email varchar(32) ); -- Primary key column create table t18( `id` int primary key, -- express id Column is a primary key column `name` varchar(32), email varchar(32) ); -- Show me composite primary keys( id and name (make composite primary key) create table t18( `id` int, `name` varchar(32), email varchar(32), primary key(id,`name`) -- Here is the composite primary key ) ; insert into t18 values(1,'tom','tom@sohu.com'); insert into t18 values(1,'jack','jack@sohu.com'); insert into t18 values(1,'tom','xx@sohu.com'); -- Error: composite primary key violation Duplicate entry '1-tom' for key 'PRIMARY' select * from t18; -- 3,There are two ways to specify a primary key -- Specify the field name directly after the field name: primary kek -- Write at the end of the table definition primary key(Listing) create table t19( `id` int, `name` varchar(32) primary key, -- Specify the field name directly after the field name: primary kek email varchar(32) ); create table t20( `id` int, `name` varchar(32), email varchar(32), primary key(`name`) -- Write at the end of the table definition primary key(Listing) ); -- 4,use desc Table name, you can see primary key Situation desc t20; -- see t20 Table results showing constraints desc t18;
Not null
If not null is defined on a column, you must provide data for the column when inserting data
Field name field type not null
Unique
When a unique constraint is defined, the column value cannot be repeated
Field name field type unique
unique details (note):
1. If not null is not specified, the unique field can have multiple nulls
2. A table can have multiple unique fields
-- unique Use of create table t21( `id` int unique, -- express id Columns cannot be repeated `name` varchar(32), email varchar(32) ); insert into t21 values(1,'jack','jack@sohu.com'); insert into t21 values(1,'tom','tom@sohu.com'); -- error Duplicate entry '1' for key 'id' select * from t21; -- unique details(be careful): -- 1,If not specified not null，be unique There can be more than one field null -- If a column(field),yes unique not null The use effect is similar primary key insert into t21 values(null,'tom','tom@sohu.com'); insert into t21 values(null,'tom','tom@sohu.com');-- success insert into t21 values(null,'tom','tom@sohu.com');-- success -- 2,A table can have more than one unique field create table t22( `id` int unique, -- express id Columns cannot be repeated `name` varchar(32) unique,-- express name Columns cannot be repeated email varchar(32) ); desc t22;
Foreign key
Used to define the relationship between the master table and the slave table: to define a foreign key constraint on the slave table, the master table must have a primary key constraint or a unique constraint. After defining a foreign key constraint, the foreign key column data must exist or be null in the primary key column of the master table
FOREIGN KEY (Field name of this table) PEFERENCES Main table name(Primary key name or unique Field name)
Schematic diagram of foreign keys:
Insert a class in the student table_ id=300 will fail because there is no record of id=300 in the class table
Deleting the record with id=200 in the class table will fail because there are 200 records in the student table,
Unless the record of student table is deleted first, the record of class table is deleted
Class table (main table) – student table (table of foreign keys)
-- Foreign key demonstration -- Create class table master table create table my_class( id int primary key, -- To define a foreign key constraint on a slave table, the master table must have a primary key constraint or`unique`constraint `name` varchar(32) not null default '' ); -- Create student table create table my_stu( id int primary key, -- Student number `name` varchar(32) not null default '', class_id int, -- Student's class number -- Specify the foreign key relationship below references: reference resources/point foreign key (class_id) references my_class(id) ); -- test data insert into my_class values(100,'java'),(200,'web'); select * from my_class; insert into my_stu values(1,'tom',100); insert into my_stu values(2,'jack',200); -- The following statement will fail Cannot add or update a child row: a foreign key constraint fails (`zzp_db02`.`my_stu`, CONSTRAINT `my_stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)) insert into my_stu values(3,'zzp',300); -- Error because class 300 does not exist select * from my_stu;
Foreign key – details
1. The field of the table pointed to by the foreign key must be primary key or unique
2. The type of the table is innodb. Only such a table supports foreign keys
3. The type of the foreign key field should be consistent with that of the primary key field (the length can be different)
4. The value of the foreign key field must have appeared in the primary key field or be null [provided that the foreign key field can be ull]
5. Once the relationship between primary and foreign keys is established, the data cannot be deleted at will
insert into my_stu values(4,'zzp',null); -- Yes, the foreign key is not written not null -- Once the relationship between primary and foreign keys is established, the data cannot be deleted at will -- Cannot delete or update a parent row: a foreign key constraint fails (`zzp_db02`.`my_stu`, CONSTRAINT `my_stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)) delete from my_class where id = 100; -- Can't delete
check
It is used to enforce the conditions that row data must meet. It is assumed that a check constraint is defined on the sal column, and the sal column value is required to be between 1000 and 2000. If it is not between 1000 and 2000, an error will be prompted
Tip: both oracle and sql server support check, but mysql5 7 at present, check is not supported. It only performs syntax validation, but it will not take effect
Basic syntax: column name type check (check condition)
The function of check in mysql is generally controlled in the program or completed through triggers.
-- demonstration check Use of
-- mysql5.7 Not yet supported`check`，Only grammatical validation, but it will not take effect
-- oracle,sql server,These two databases are really effective
-- test
create table t23(
id int primary key,
`name` varchar(32),
sex varchar(6) check (sex in ('man','woman')),
sal double check (sal > 1000 and sal <2000)
);
-- Add data
insert into t23 values(1,'jack','mid',1); -- success
select * from t23;
Store watch design
There is a database shop of a commodity store_ DB, which records customers and their shopping, consists of the following three tables:
Goods (commodity number goods_id, commodity name goods_name, unit price, commodity category, supplier)
Customer (customer No., customer_id, name, address, email, sex, ID card_id)
Purchase (purchase order No. order_id, customer No. customer_id, commodity No. goods_id, purchase quantity nums)
1. In the definition, it is required to state [carry out reasonable design]:
(1) Primary foreign key for each table
(2) Customer name cannot be null
(3) Email cannot be repeated
(4) Customer gender [male] [female] check enumeration
(5) The unit price is between 1.0 and 9999.99
-- Exercises using constraints
-- Create database
create database shop_db;
/*
There is a database shop of a commodity store_ DB, which records customers and their shopping, consists of the following three tables:
Goods (commodity number goods_id, commodity name goods_name, unit price, commodity category, supplier)
Customer (customer No., customer_id, name, address, email, sex, ID card_id)
Purchase (purchase order No. order_id, customer No. customer_id, commodity No. goods_id, purchase quantity nums)
1,In the definition, it is required to state [carry out reasonable design]:
(1)Primary foreign key for each table
(2)Customer name cannot be null
(3)Email cannot be repeated
(4)Customer gender [male] [female] check enumeration
(5)The unit price is between 1.0 and 9999.99
*/
-- Create item table
create table goods(
goods_id int primary key,
goods_name varchar(64) not null default '',
untiprice decimal(10.2) not null default 0
check (untiprice >= 1.0 and untiprice <= 9999.99),
category int not null default 0,
provider varchar(64) not null default ''
);
-- Create customer table
create table customer(
customer_id char(8) primary key, -- Programmer defined
`name` varchar(64) not null default '',
address varchar(64) not null default '',
email varchar(64) unique not null,
sex enum('male','female') not null, -- Using enumeration type is effective
card_id char(18)
);
-- Create purchase table
create table purchase(
order_id int unsigned primary key,
customer_id char(8) not null default '', -- Foreign key constraint after
goods_id int not null default 0, -- Foreign key constraint after
nums int not null default 0,
foreign key (customer_id) references customer(customer_id),
foreign key (goods_id) references goods(goods_id)
);
desc goods;
desc customer;
desc purchase;
Self growth
In a table, there is an id column (integer). We hope that when adding records, the column will start from 1 and grow automatically. What should we do Self growth
Basic syntax:
Field name integer primary key auto_increment
Add self growing field method
insert into xxx (Field 1, Field 2) values (null, 'value'...);
insert into xxx (Field 2, ...) values ('Value 1', 'Value 2'...);
insert into xxx values (null, 'value'...);
-- Demonstrate the use of self growth
-- Create table
create table t24(
id int primary key auto_increment,
email varchar(32) not null default '',
`name` varchar(32) not null default ''
);
-- Test the use of self growth
insert into t24 values(null,'jack@qq.com','jack');
insert into t24 values(null,'tom@qq.com','tom');
insert into t24 (email,`name`) values('zzp@qq.com','zzp');
desc t24;
select * from t24;
Self growth usage details
1. Generally speaking, self growth is used in conjunction with the primary key
2. Self growth can also be used alone [but with a unique]
3. The fields decorated by self growth are of integer type (although decimals can also be used, they are rarely used in this way)
4. By default, the growth starts from 1. You can also modify it through the following commands
alter table name auto_increment = new start value;
5. If you specify a value for the self growth field (column) when adding data, the specified value shall prevail. If self growth is specified, it is generally added according to the self growth rules
-- new table create table t25( id int primary key auto_increment, email varchar(32) not null default '', `name` varchar(32) not null default '' ); -- Modify the default start from growth value alter table t25 auto_increment = 100; insert into t25 values(null,'jack@qq.com','jack'); -- If you add data, give it to the self growth field(column)If the specified has a value, the specified value shall prevail insert into t25 values(666,'zzp@qq.com','zzp'); select * from t25;
mysql index
When it comes to improving database performance, indexing is the best and cheapest thing. Without adding memory, changing programs and adjusting sql, the query speed can be increased by a hundred times.
Give an example to illustrate the benefits of indexing [building a massive table of 8000000]
-- Create test database tmp create database tmp; # Department table CREATE TABLE `dept` ( `deptno` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0', `dname` VARCHAR(20) NOT NULL DEFAULT '', `loc` VARCHAR(13) NOT NULL DEFAULT '' ) ENGINE=INNODB DEFAULT CHARSET=utf8; # Employee table CREATE TABLE `emp` ( `empno` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',/*number*/ `ename` VARCHAR(20) NOT NULL DEFAULT '',/*name*/ `job` VARCHAR(9) NOT NULL DEFAULT '',/*work*/ `mgr` MEDIUMINT(8) UNSIGNED DEFAULT 0,/*Superior number*/ `hiredate` DATE NOT NULL,/*Entry time*/ `sal` DECIMAL(7,2) NOT NULL,/*Salary*/ `comm` DECIMAL(7,2) DEFAULT NULL,/*dividend*/ `deptno` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' ) ENGINE=INNODB DEFAULT CHARSET=utf8; # Wage scale CREATE TABLE `salgrade` ( `grane` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0', `losal` DECIMAL(17,0) NOT NULL, `hisal` DECIMAL(17,0) NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8; # test data INSERT INTO salgrade VALUES(1,700,1200); INSERT INTO salgrade VALUES(2,1201,1400); INSERT INTO salgrade VALUES(3,1401,2000); INSERT INTO salgrade VALUES(4,2001,3000); INSERT INTO salgrade VALUES(5,3001,4000); DELIMITER $$ # Create a function named rand_string, which can randomly return a numeric string specified by me CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) # This function returns a string BEGIN # A variable chars is defined_ STR, type varchar(100) # Default to chars_str initial value 'abcdefghijklmnopqrstuvwxyxz' DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYXZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO # concat function: join function mysql function SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND()*52), 1)); SET i = i + 1; END WHILE; RETURN return_str; END $$ # Here we define a function to return a random department number CREATE FUNCTION rand_num() RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(10 + RAND()*500); RETURN i; END $$ # Create a stored procedure to add employees CREATE PROCEDURE insert_emp(IN START INT(10), IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; # set autocommit = 0 set aitocommit to 0 # autocommit = 0 meaning: do not submit automatically SET autocommit = 0; #sql statements are not submitted by default REPEAT SET i = i + 1; # The string and department number are randomly generated through the previously written function, and then added to the emp table INSERT INTO emp VALUES ((START + i), rand_string(6), 'SALESMAN', 0001, CURDATE(), 2000, 400, 3); UNTIL i = max_num END REPEAT; # commit submits all sql statements as a whole to improve efficiency COMMIT; END $$ # Add 8000000 data CALL insert_emp(100001,8000000)$$ # Command terminator, and then reset to; DELIMITER ;
The above implementation may take 7 or 8 minutes. Please wait patiently
In the local folder, you can see the size of the table generating the database: 635M
Query emp quantity
Randomly query an emp data and see the response time: 3.844 seconds
SELECT COUNT(*) FROM emp; -- When no index is created, a data record is queried randomly SELECT * FROM emp WHERE empno = 123456; -- Use index to optimize and experience the benefits of index -- Before the index is created, emp.ibd File size 635 m -- After index creation emp.ibd File size 667 m [The index itself takes up space] -- empno_index Index name -- ON emp (empno) : express emp Tabular empno Create index for column CREATE INDEX empno_index ON emp (empno);
After the index is created, the IBD file becomes larger:
Query speed after index creation: 0.004 seconds
Is it possible to build an index to solve the use problem?
What happens if there is no index on ename?
select * from emp where ename = 'axJxC';
Principle of index
Why is it slow without an index? [because it will scan the whole table]
Why is indexing fast? [it will form an index data structure, such as binary tree, etc.]
Cost of indexing
- 1. Disk occupancy
- 2. Impact on the efficiency of dml(update delete insert) statements
In the project, select(90%) is more than update, delete and insert (10%)
Type of index
1. Primary key index: the primary key is automatically the primary index (type: primary key)
2. UNIQUE index
3. General INDEX (INDEX)
4. FULLTEXT [for MySAM]
For general development, the full-text index provided by mysql is not used, but full-text search Solr and elastic search (ES)
create table t1( in int primary key, -- A primary key is also an index, which is called a primary key index `name` varchar(32) ); create table t2( in int unqiue, -- id Is unique and also an index, called unique Indexes `name` varchar(32) );
Index usage
1. Add index (build test table id, name)
create [UNIQUE] index_name on table_name (col_name[(length)] [ASC|DESC], ...)
alter table table_name ADD INDEX [index_name] (index_col_name, ...)
2. ADD PRIMARY KEY (index) ALTER TABLE table name ADD PRIMARY KEY (column name,...)
3. Delete index
DROP INDEX index_name ON table_name;
alter table table_name drop index index_name;
4. Deleting a primary key index is special:
alter table table_name drop primary key;
5. Query index (three methods)
show index(es) from table_name;
show keys from table_name'
desc tales_name;
-- demonstration mysql Index usage for -- Create table create table t25( id int, `name` varchar(32) ); -- Whether the query table has an index show index from t25; -- Add index -- Add unique index create unique index id_index on t25 (id); -- Add normal index method 1 create index id_index on t25 (id); -- How to select an index -- 1. If the value of a column will not be repeated, it is preferred to use unique Index, otherwise use normal index -- Add normal index method 2 alter table t25 add index id_index (id); -- Add primary key index -- Add fields directly when creating a table primary key create table t26( id int primary key, `name` varchar(32) ); -- perhaps alter table t26 add primary key (id); show index from t26; -- Delete index drop index id_index on t25; -- Delete primary key index alter table t26 drop primary key; -- To modify an index, first delete the index and then add a new index -- Query index -- 1. Mode 1 show index from t26; -- 2. Mode 2 show indexes from t25; -- 3. Mode 3 show keys from t25; -- 4. Mode 4 desc t25;
Summary
Which columns are suitable for indexes
1. More frequent fields as query criteria should be indexed
select * from emp where empno = 1;
2. Fields with poor uniqueness are not suitable for creating indexes alone, that is, they are frequently used as query criteria
select * from emp where sex = 'male';
3. Fields that update very frequently are not suitable for index creation
select * from emp where logincount = 1;
4. Fields that do not appear in the where clause should not be indexed
mysql transaction
What is a transaction
Transaction is used to ensure the consistency of data. It is composed of a group of related dml statements. All dml statements in this group either succeed or fail. For example, transfer should be handled by transaction to ensure the consistency of data.
Transactions and locks
When performing a transaction operation(dml sentence)，mysql It will lock the table to prevent other users from changing the data of the table. At this time, it is very important for users to speak
Several important operations of mysql database console transaction
- 1. start transaction – start a transaction
- 2. Savepoint savepoint name – sets the savepoint
- 3. rollback to save roll call – rollback transaction
- 4. Rollback – rollback all transactions
- 5. Commit – commit the transaction. All operations take effect and cannot be rolled back
Details:
1. No savepoint set
2. Multiple savepoints
3. Storage engine
4. Start transaction mode
Fallback transaction
Before introducing fallback transactions, let's talk about savepoints( savepoint). Savepoints are points in a transaction,Used to cancel some transactions. When the transaction ends, all savepoints defined by the transaction will be automatically deleted. When performing a fallback transaction, you can fallback to the specified point by specifying a savepoint.
Commit transaction
use commit Statement can commit a transaction when executed commit After the statement, it will confirm the change of the transaction, end the transaction, delete the savepoint, release the lock and make the data effective. When used commit Statement after the transaction ends, other sessions[Other connections]You can view the new data after the transaction[All data will come into force]
-- Transaction is an important concept and specific operation -- demonstration -- 1. Create a test table create table t27( id int, `name` varchar(32) ); -- 2. Open transaction start transaction -- 3. Set save point savepoint a -- implement dml operation insert into t27 values(100,'tom'); select * from t27; -- 1 Records -- Set save point savepoint b -- implement dml operation insert into t27 values(200,'jack'); select * from t27; -- 2 Records -- 4. Back to b rollback to b select * from t27;-- 1 Records -- Continue back to a rollback to a select * from t27;-- 0 Records -- If so rollback Indicates a direct fallback to the start of the transaction rollback -- commit The transaction is committed, all operations take effect, and cannot be rolled back (the savepoint has been deleted) commit
Transaction details discussion
1. If the transaction is not started, the dml operation is automatically committed by default and cannot be rolled back
2. If you start a transaction without creating a savepoint, you can execute rollback. By default, it is back to the state where your transaction started
3. You can also create multiple savepoints in this transaction (before committing). For example: savepoint aa; Execute DML, savepoint BB;
4. You can choose which savepoint to go back to before the transaction is committed.
5. The transaction mechanism of mysql needs innodb's storage engine before it can be used. myisam is not easy to use.
6. Start a transaction start transaction or set autocommit=off;
-- Transaction details -- 1. If you do not start a transaction, by default, dml The operation is submitted automatically and cannot be rolled back insert into t27 values(300,'milan'); -- Auto submit commit select * from t27; -- 2. If you start a transaction and you don't create a savepoint, you can execute it rollback， -- The default is to go back to the beginning of your transaction start transaction insert into t27 values(400,'king'); insert into t27 values(500,'scott'); rollback -- Indicates a direct fallback to the start of the transaction commit -- 3. You can also be in this business(Before submission)，Create multiple savepoints. -- For example: savepoint aa; implement dml , savepoint bb; -- 4. You can choose which savepoint to go back to before the transaction is committed. -- 5. InnoDB The storage engine supports transactions, MyISM I won't support it -- 6. Start a transaction start transaction perhaps set autocommit=off;
mysql transaction isolation level
Introduction to transaction isolation level
1. When multiple connections open their own transactions to operate the data in the database, the database system shall be responsible for isolation operation to ensure the accuracy of each connection in obtaining data.
2. If isolation is not considered, the following problems may arise:
- Dirty reading
- Non repeatable reading
- Unreal reading
View transaction isolation level
Dirty read: a dirty read is generated when a transaction reads a change (update,insert,delete) that is still committed by another transaction
nonrepeatable read: the same query is performed multiple times in the same transaction. Due to the modification or deletion made by other committed transactions, different result sets are returned each time. This occurrence is nonrepeatable.
phantom read: the same query is performed multiple times in the same transaction. Due to the insertion operation of other submission transactions, different result sets are returned each time. phantom read occurs at this time.
Transaction isolation level
Concept: Mysql isolation level defines the isolation degree between transactions.
| Mysql isolation level | Dirty reading | Non repeatable reading | Unreal reading | Lock read |
|---|---|---|---|---|
| Read uncommitted | √ | √ | √ | Unlocked |
| Read committed | × | √ | √ | Unlocked |
| Repeatable read | × | × | × | Unlocked |
| Serializable | × | × | × | Lock |
Description: √ possible × Will not appear
Transaction isolation level of mysql - Case
The following demonstrates the effect of transaction isolation level:
Query the transaction isolation level of console A:
Set the isolation level of B console
The query of isolation level of console A and console B shows that console A [can be read repeatedly] console B [read not submitted]
A. Start transaction on console B
Create A table in console A (zzp#u db02 database):
A. B console check table:
After adding A record in console A, console B queries [uncommitted transaction] (dirty reading) - [read uncommitted isolation level]
Update and add operations on console A, and then submit the transaction [commit]. The query result of console B is [uncommitted] (there are non repeatable reads and phantom reads) - [read uncommitted isolation level]
B console commit transaction:
Console A starts the transaction, and console B sets the isolation level to Read committed and starts the transaction
Console A adds A new piece of data, and console B queries [uncommitted transaction] (no dirty reading) - [read submitted isolation level]
Console A modifies A piece of data and submits the transaction. Console B queries [uncommitted transaction] (non repeatable read and phantom read) - [read submitted isolation level]
B console commit transaction:
Console A starts the transaction, console B sets the isolation level to Repeatable read and starts the transaction
Console A adds and modifies data, and then submits the transaction. Console B queries [uncommitted transaction] (no dirty read, non repeatable read and phantom read) - [repeatable read isolation level]
B console commit transaction:
Console A starts the transaction, and console B sets the isolation level to (Serializable) Serializable and starts the transaction
Console A adds and modifies data, console B cannot find data, and displays error message when it times out – [serializable isolation level]
As soon as console A commits the transaction, the query result of console B – [serializable isolation level]
-- demonstration mysql Transaction isolation level -- 1. Open two mysql Console -- 2. View current mysql Isolation level of select @@tx_isolation; -- mysql> select @@tx_isolation; -- +-----------------+ -- | @@tx_isolation | -- +-----------------+ -- | REPEATABLE-READ | -- +-----------------+ -- 3.Set the isolation level of one of the consoles Read uncommitted -- Read committed -- Repeatable read -- Serializable set session transaction isolation level READ UNCOMMITTED; -- Two mysql Console open transaction start transaction; -- 4. Create in table zzp_db02 In database create table `account`( id int, `name` varchar(32), money int ); select * from account; -- Add record insert into account values (100,'tom',1000); -- modify update account set money = 800 where id = 100; insert into account values (200,'jack',2000);
1. View current session isolation level
select @@tx_isolation;
2. View the current isolation level of the system
select @@global.tx_isolation;
3. Set current session isolation level
set session transaction isolation level repeatable read;
4. Sets the current isolation level of the system
set global transaction isolation level repeatable read;
5. The default transaction isolation level of mysql is repeatable read. Generally, there are no special requirements and do not modify it (because this level can meet the needs of most projects)
-- 1,View current session isolation level select @@tx_isolation; -- 2,View the current isolation level of the system select @@global.tx_isolation; -- 3,Set current session isolation level set session transaction isolation level repeatable read; -- 4,Sets the current isolation level of the system set global transaction isolation level [The level you set];
Global modification, modify mysql Ini configuration file, add
#The optional parameters are: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE [mysqld] transaction-isolation = REPEATABLE-READ
mysql transaction ACID
acid attribute of transaction
1. Atomicity
Atomicity means that a transaction is an inseparable unit of work, and operations in a transaction either occur or do not occur.
2. Consistency
Transactions must transition the database from one consistency state to another.
3. Isolation
Transaction isolation is that when multiple users access the database concurrently, the database opens transactions for each user, which can not be disturbed by the operation data of other transactions. Multiple concurrent transactions should be isolated from each other.
4. Durability
Persistence means that once a transaction is committed, its changes to the data in the database are permanent. Then, even if the database fails, it should not have any impact on it.
mysql table type and storage engine
Basic introduction
1. The table type of MySQL is determined by Storage Engines, mainly including MyISAM, innoBD, Memory, etc
2. MySQL data tables mainly support six types: CSV, Memory, ARCHIVE and MRG_MYISAM,MyISAM,InnoBDB.
3. These six types are divided into two categories. One is "transaction safe", such as InnoDB; The rest belong to the second category, which is called "non transaction safe" [mysiam and memory]
Display all storage engines supported by the current database:
show engines;
Main storage engine / table type characteristics
| characteristic | Myisam | InnoDB | Memory | Archive | |
|---|---|---|---|---|---|
| Speed of batch insertion | high | low | high | Very high | |
| Transaction security | support | ||||
| Full text index | support | ||||
| Lock mechanism | Watch lock | Row lock | Watch lock | Row lock | |
| Storage limit | No, | 64TB | have | No, | |
| B-tree index | support | support | support | ||
| Hash indices | support | support | |||
| Cluster index | support | ||||
| Data cache | support | support | |||
| Index cache | support | support | support | ||
| Data compressible | support | support | |||
| Space use | low | high | N/A | Very low | |
| Memory usage | low | high | secondary | low | |
| Support foreign keys | support |
Details
These include MyISAM, InnoDB and MEMORY
1,MyISAM It does not support transactions or foreign keys, but it has fast access speed and does not require transaction integrity 2,InnoDB The storage engine provides transaction security with specific commit, rollback and crash recovery capabilities. But compared to MyISAM Storage engine, InnoDB Write processing is less efficient and takes up more disk space to Preserve data and indexes. 3,MEMORY The storage engine creates tables using what is in memory. each MEMORY The table only corresponds to the actual data A disk file. MEMORY The access of type table is very fast because its data is stored in memory, And it is used by default HASH Indexes. But once MYSQL When the service is shut down, the data in the table will be lost, and the structure of the table is still there.
Three storage engine table use cases
-- innoDB The storage engine is the one mentioned earlier -- 1. Support transaction 2. Support foreign key 3. Row level locks are supported -- myisam Storage engine create table t28( id int, `name` varchar(32) ) engine MYISAM; -- 1. Fast addition speed 2. Foreign keys and transactions are not supported. 3. Support table level lock -- Test transaction start transaction; savepoint t1; insert into t28 values(1,'tom'); select * from t28; rollback to t1; -- RollBACK t1 select * from t28; -- There is still a record -- memory Storage engine -- 1. Data is stored in memory[close mysql Service, data is lost, but the table structure is still there] -- 2. Fast execution (no IO Reading and writing) 3. Default support index( hash Table) create table t29( id int, `name` varchar(32) ) engine memory; insert into t29 values(1,'jack'),(2,'tom'),(3,'zzp'); select * from t29; -- 3 Records -- Demo effect off on mysql service desc t29; select * from t29; -- 0 Records
How to select a storage engine for a table
1,If your application doesn't need transactions, it's just basic CRUD Operation, then MyISAM It's the only choice, fast 2,If you need to support transactions, select InnDB 3,Memory The storage engine is to store data in memory, because there is no disk I./O Waiting, Very fast. However, since it is a memory storage engine, any modifications made will disappear after the server is restarted. (classic usage) user's online status().)
Modify storage engine
alter table 'table name' ENGINE = storage ENGINE;
view
Look at a demand
emp There are a lot of column information in the table, some of which are important personal information (such as sal，comm，mgr，hiredate)， If we want a user to only query emp Tabular( empno,ename，job and deptno)Information, What can I do? => view
Basic concepts
1. A view is a virtual table with query definitions. Like real tables, views include columns whose data comes from the corresponding real table (base table)
2. Schematic diagram of the relationship between view and base table
Basic use of view
1. Create: create view name as select statement
2. Modify: alter view view name as select statement
3. View: SHOW CREATE VIEW view view name
4. Delete: drop view view name 1, view name 2
Create a view emp_view01, you can only query the (empno,ename,job and deptno) information of the emp table
-- Use of views -- Create a view emp_view01，Only query emp Tabular( empno,ename,job and deptno)information -- Create view create view emp_view01 as select empno, ename, job, deptno from emp; -- view a chart desc emp_view01; -- Query view emp_view01 surface select * from emp_view01; select empno,job from emp_view01; -- Modify view alter view emp_view01 as select empno, ename, job, sal, deptno from emp; select * from emp_view01; -- view a chart show create view emp_view01; -- Delete view drop view emp_view01;
View detail discussion
1. After creating a view, go to the database to see that the corresponding view has only one view structure file (form: view name. frm)
2. The data change of the view will affect the base table, and the data change of the base table will also affect the view [insert update delete]
----For the previous employee management system--- create view myview as select empno,ename,job,comm from emp; select * from myview; update myview set comm = 200 where empno = 7369;//Modify the view and make changes to the base table update emp set comm = 100 where empno = 7369;//Modifying the base table also changes the view
3. The view can be used in the view, and the data still comes from the base table
-- View details -- 1,After creating a view, go to the database to see that the corresponding view has only one view structure file (form: view name).frm) -- 2,The data change of the view will affect the base table, and the data change of the base table will also affect the view[insert update delete] -- Modifying the view affects the base table create view myview as select empno,ename,job,comm from emp; select * from myview; update myview set job = 'MANAGER' where empno = 7369; select * from myview; select * from emp; -- Modifying the base table will affect the view update emp set job = 'SALESMAN' where empno = 7369; -- 3,Views can be used in, for example, from myview In the view, select two columns empno,ename Make a new view desc myview; create view myview02 as select empno,ename from myview; desc myview02; select * from myview02;
View best practices
1. Safe. Some data sheets have important information. Some fields are confidential and cannot be seen directly by users. In this case, only one field can be created in this view. In this way, users can query the fields they need instead of viewing confidential fields.
2. Performance. The data of relational database is often stored in tables. Using foreign keys will establish the relationship between these tables. At this time, database queries usually use joins. This is not only troublesome, but also relatively inefficient. If you build a view and combine related tables and fields, you can avoid using JOIN to query data.
3. Flexibility. If there is an old table in the system, it will be abandoned due to design problems. However, many applications are based on this table and are not easy to modify. At this time, you can create a view, and the data in the view is directly mapped to the new table. In this way, many changes can be made less and the purpose of upgrading the data table can be achieved.
View exercise
Create a view (emp_view03) for the three tables of emp, dept and salgrade, which can display employee number, employee name, employee department name and salary level [that is, use three tables to create a view]
-- practice: -- in the light of emp, dept and salgrade These three tables create a view(emp_view03)， -- You can display employee number, employee name, employee department name and salary level[Three tables are used to create a view] /* Analysis: use three tables to jointly query and get the results Build the results into a view */ -- Use three tables to jointly query and get the results select empno, ename, dname, grane from emp, dept, salgrade where emp.deptno = dept.deptno and (sal between losal and hisal); -- Build the results into a view create view emp_view03 as select empno, ename, dname, grane from emp, dept, salgrade where emp.deptno = dept.deptno and (sal between losal and hisal); desc emp_view03; select * from emp_view03;
Mysql user
Users in mysql are stored in the system database User table
Description of important fields in user table:
1. host: the "location" where login is allowed. localhost means that the user is only allowed to log in locally. You can also specify an ip address, such as 192.168.1.100
2. User: user name
3,authentication_string: password, which is the password encrypted through the password() function of mysql.
Create user
create user 'user name' @ 'allowed login location' identified by 'password'
Description: create a user and specify a password at the same time
-- Mysql user management
-- Reason: when we do project development, we can assign corresponding to different developers Mysql Operation authority
-- So, Mysql Database Administrator( root)，Create different users as needed and assign corresponding permissions for developers to use
-- 1. Create a new user
-- Interpretation (1)'zzp'@'localhost' Represents the complete information of the user 'zzp' user name 'localhost' Logged in IP
-- (2)'zzp' Password, but pay attention to storage mysql.user Table, yes password('zzp') Encrypted password
create user 'zzp'@'localhost' identified by 'zzp';
-- query password('zzp') Encrypted password
select password('zzp');
select `host`,`user`,authentication_string from mysql.user;
-- 2. delete user
drop user 'zzp'@'localhost';
Log in to the new user:
delete user
drop user 'user name' @ 'allowed login location';
User change password
Change your password:
set password = password('password ');
Modify someone else's password (user permission is required):
set password for 'user name' @ 'login location' = password('password ');
-- Change your password
set password = password('zzp123');
-- You need permission to change someone else's password
-- If the permission is insufficient, an error is reported: Access denied for user 'zzp'@'localhost' to database 'mysql'
set password for 'zzp'@'localhost' = password('zzp');
Permissions in mysql
| jurisdiction | significance |
|---|---|
| All/All Privileges | Represents all permissions at the global or database wide object level |
| Alter | Permission represents the permission to modify the table structure, but it must be matched by create and insert permissions. If it is a rename table name, you must have the permission to alter and drop the original table, create and insert the new table |
| Alter routine | Represents the permission to modify or delete stored procedures and functions |
| Create | Represents permission to create new databases and tables |
| Create routine | Represents permission to create stored procedures and functions |
| Create tablespace | Represents permission to create, modify, and delete tablespaces and log groups |
| Create temporary tables | Represents permission to create temporary tables |
| Create user | Represents the permission to create, modify, delete and rename user |
| Create view | Represents permission to create views |
| Delete | Represents permission to delete row data |
| Drop | Represents the permission to delete databases, tables and views, including the truncate table command |
| Event | Allows querying, creating, modifying and deleting MySQL events |
| Execute | Represents permission to execute stored procedures and functions |
| File | It refers to the operation of reading and writing disk files in the directory accessible to MySQL. The available commands include load data infile,select... into outfile,load file() function |
| Grant option | Represents permission to execute stored procedures and functions |
| Index | Represents whether indexes are allowed to be created and deleted |
| Insert | It indicates whether data can be inserted into the table. At the same time, insert permission is also required when executing analyze table, optimize table and repair table statements |
| Lock | The delegate allows locking of a table with select permission to prevent other links from reading or writing to the table |
| Process | The representative allows you to view the process information in MySQL, such as executing show processlist, mysqladmin processlist, show engine and other commands |
| Reference | It is introduced after version 5.7.6 and represents whether foreign keys can be created |
| Reload | Indicates that the flush command is allowed to be executed, indicating that the permission table is reloaded into the system memory. The refresh command indicates that the log file is closed and reopened, and all tables are refreshed |
| Replication client | The representative is allowed to execute the commands show master status, show slave status and show binary logs |
| Replication slave | The representative allows the slave host to connect to the master through this user in order to establish a master-slave replication relationship |
| Select | It means that data can be viewed from the table. This permission is not required for some select executions that do not query table data, such as Select 1+1, Select PI()+2; Moreover, the select permission is also required when the update/delete statement contains the where condition |
| Show databases | The representative can view all database names by executing the show databases command |
| Show view | Represents to view the statement created by the view by executing the show create view command |
| Shutdown | Represents that the database instance is allowed to be closed, and the execution statements include mysqladmin shutdown |
| Super | The representative is allowed to execute a series of database management commands, including kill to forcibly close a connection, change master to create a replication relationship, and create/alter/drop server |
| Trigger | Represents the permission to create, delete, execute and display triggers |
| Update | Represents permission to modify data in a table |
| Usage | Is the default permission after creating a user, which itself represents the connection login permission |
Authorize users
Basic syntax:
grant permission column on library Object name to 'user name' @ 'login location' [identified by 'password']
explain:
1. Permission list. Multiple permissions are separated by commas
grant select on ... grant select, delete, create on ... grant all [privileges]on ... //Indicates that the user is given all permissions on the object
2. Special note
*. *: all objects (tables, views, stored procedures) representing all databases in the system
Library. *: Represents all data objects (tables, views, stored procedures) in a database
3. Identified by can be omitted or written
(1) If the user exists, it is to modify the password of the user
(2) If the user does not exist, create the user!
Reclaim user authorization
Basic syntax:
revoke permission list on library Object name from 'user name' @ 'login location';
Authority effective instruction
If the permission does not take effect, you can execute the following command
Basic grammar
flush privileges;
practice
User management exercise
1. Create a user (your name, Pinyin), password 123, and can only log in locally. Do not log in to mysql remotely
2. To create the news class under the library and table, it is required to use the root user to create it
3. Assign users permission to view the news table and add data
4. Test to see if the user has these permissions
5. Change the password to abc. Requirements: use the root user to complete
6. Login again
7. Recycle permission
8. Use the root user to delete your user
root user
-- Demonstrate user rights management
-- Create user zzp01 Password 123
create user 'zzp01'@'localhost' identified by '123';
-- use root User creation testdb,surface news
create database testdb;
create table testdb.news(
id int,
`content` varchar(32)
);
-- Add a piece of data
insert into testdb.news values (100, 'China News');
select * from testdb.news;
-- to zzp01 Assign view news Table and add news Permissions for
grant select, insert on testdb.news to 'zzp01'@'localhost';
-- You can add permissions
grant update on testdb.news to 'zzp01'@'localhost';
-- modify zzp01 Your password is abc
set password for 'zzp01'@'localhost' = password('abc');
-- recovery zzp01 User in testdb.news All permissions for the table
revoke select, update, insert on testdb.news from 'zzp01'@'localhost';
-- perhaps
revoke all on testdb.news from 'zzp01'@'localhost';
-- delete zzp01 user
drop user 'zzp01'@'localhost';
zzp01 user
-- Here, by default, zzp01 Users can only see one default system database SELECT * FROM testdb.news; INSERT INTO testdb.news VALUES (200, 'shanghai News'); -- Whether it can be modified or not delete -- Error report modification: UPDATE command denied to user 'zzp01'@'localhost' for table 'news' UPDATE news SET content = 'chengdu News' WHERE id = 100; -- When root give update Operation after permission UPDATE news SET content = 'chengdu News' WHERE id = 100; -- Successful execution: 1 line in total is affected
Test zzp01 user login
When the root user gives zzp01 user permission, the following information will be displayed after refreshing:
When the root user reclaims all the permissions of zzp01 user, refresh:
After the root user deletes zzp01 user, connect again:
Details
1. When creating a user, if the Host is not specified, it will be%,% means that all IP S have connection permission
create user xxx;
2. You can also specify this
create user 'xxx'@'192.168.1.%' Indicates that XXX user is at 192.168.1* You can log in to mysql with your IP address
3. When deleting a user, if the host is not%, you need to specify the 'user' @ 'host value'
-- Explain user management details -- 1,When creating a user, if you do not specify Host，Then%，%Indicates all IP All have connection permission -- create user xxx; create user 'zzp02'; select `host`,`user` from mysql.user; -- 2,You can also specify this -- create user 'xxx'@'192.168.1.%' express xxx User at 192.168.1.* of IP Can log in mysql create user 'zzp02'@'192.168.1.%'; -- 3,When deleting a user, if host no %，Need to be clearly specified 'user'@'host value' drop user 'zzp02'; drop user 'zzp02'@'192.168.1.%';
task
1. Multiple choice questions:
(1) Which of the following statements is wrong? D A. select empno, ename name, sal salary from emp; -- correct ename name, sal salary Can be omitted as B. select empno, ename, sal as salary from emp; -- correct C. select ename, sal*12 as "Annual Salary" from emp; -- correct D. select eame, sal*12 Annual Salary from emp; -- error sal*12 Annual Salary : sal*12 My alias is Annual But there is a space after it Salary Uncertain column (2) Which statement should a user use to display the information of all employees whose subsidy is not empty? B A. select ename, sal, comm from emp where comm <> null; --Error should not be used<> (Not equal to)judge null B. select ename, sal, comm from emp where comm is not null; -- correct C. select ename, sal, comm from emp where comm <> 0; -- Wrong judgment is not 0 (3) Which of the following statements is wrong? A. select ename, sal salary from emp order by sal; -- correct B. select ename, sal salary from emp order by salary; -- correct C. select ename, sal salary from emp order by 3; -- error order by 3: The column or alias to be specified cannot be 3
2. Write sql statements to view the structure of dept table and emp table
-- Write view dept Table and emp Table structure sql sentence desc dept; desc emp; show create table dept; show create table emp;
3. Complete with a simple query statement:
(1) Show all department names
(2) Display all employees and their annual income 13 (salary + allowance) and specify the column alias "annual income"
-- (1)Show all department names select dname from dept; select 100 + null from dual; -- be careful: xx + null = null -- (2)Show all employees and their annual income 13 (wages)+Subsidies) and specify the column alias "annual income" -- be careful: sal + null = null comm Need to add blame null judge select ename, (sal + ifnull(comm,0)) *13 as "annual income" from emp;
4. Restrict query data
(1) Displays the name and salary of employees whose salary exceeds 2850
(2) Displays all employees and wages whose wages are not between 1500 and 2850
(3) Displays the name and department number of the 7566 employee
(4) Displays the names and wages of employees whose wages exceed 1500 on the 10th and 30th of the Department
(5) Displays the name and position of employees without administrators
-- (1)Displays the name and salary of employees whose salary exceeds 2850 select ename,sal from emp where sal > 2850; -- (2)Displays all employees and wages whose wages are not between 1500 and 2850 select ename,sal from emp where sal < 1500 or sal > 2850; -- perhaps select ename,sal from emp where not (sal >= 1500 and sal <= 2850); -- (3)Displays the name and department number of the 7566 employee select ename, deptno from emp where empno = 7566; -- (4)Displays the names and wages of employees whose wages exceed 1500 on the 10th and 30th of the Department select ename, job, sal from emp where (deptno = 10 or deptno = 30) and sal > 1500; -- perhaps select ename, job, sal from emp where deptno in (10,30) and sal > 1500; -- (5)Displays the name and position of employees without administrators select ename, job from emp where mgr is null;
5. Sort data
(1) Displays the name, position and date of employment of employees employed between February 1, 1991 and May 1, 1991, sorted by employment.
(2) Display the names, wages and subsidies of all employees receiving subsidies, and sort them in descending order of wages
-- (1)Displays the name, position and date of employment of employees employed between February 1, 1991 and May 1, 1991, sorted by employment. select ename, job, hiredate from emp where hiredate >= '1991-01-02' and hiredate <= '1991-05-01' order by hiredate asc; -- (2)Display the names, wages and subsidies of all employees receiving subsidies, and sort them in descending order of wages select ename, sal, comm from emp order by sal desc;
6. Write the correct employee table according to sql: emp
(1) Select all employees in department 30
(2) List the names, numbers and department numbers of all clerks (CLERK)
(3) Identify employees whose commissions are higher than their salaries
(4) Identify employees whose Commission is more than 60% of their salary
(5) Find out the details of all managers in department 10 and clerks in department 20
(6) Find out the details of all managers in department 10, all clerks in department 20, and all employees who are neither managers nor clerks but whose new fund is greater than or equal to 2000
(7) Find out the different jobs of employees who receive commissions
(8) Identify employees who do not charge a commission or receive a commission of less than 100
(9) Identify the employees employed on the penultimate day of each month
(10) Identify employees who were hired earlier than 12 years ago
(11) Displays the names of all employees in lowercase
(12) Displays the name of an employee with exactly 5 characters
-- (1)Select all employees in department 30
select * from emp where deptno = 30;
-- (2)List all clerks( CLERK)Name, number and department number of
select ename, empno, deptno, job from emp where job = 'CLERK';
-- (3)Identify employees whose commissions are higher than their salaries
select * from emp where ifnull(comm,0) > sal;
-- (4)Find out that the Commission is 60% higher than the salary%Employees
select * from emp where ifnull(comm,0) > sal * 0.6;
-- (5)Find all managers in department 10( MANAGER)And all clerks in department 20( CLERK)Details of
select * from emp where
(deptno = 10 and job = 'MANAGER') OR (deptno = 20 and job = 'CLERK');
-- (6)Find all managers in department 10( MANAGER)，All clerks in department 20( CLERK)，
-- There are also details of all employees who are neither managers nor clerks, but whose new fund is greater than or equal to 2000
select * from emp where
(deptno = 10 and job = 'MANAGER') OR (deptno = 20 and job = 'CLERK')
OR (job != 'MANAGER' and job != 'CLERK' and sal >= 2000);
-- (7)Find out the different jobs of employees receiving commissions
select distinct job from emp where comm is not null;
-- (8)Identify employees who do not charge a commission or receive a commission of less than 100
select * from emp where comm is null or ifnull(comm,0) < 100;
-- (9)Identify the employees employed on the penultimate day of each month
-- Tips: last_day(date)，You can return to the last day of the month in which the date is changed
-- last_day(date) - 2 Get the date the penultimate day of all months
select last_day('2011-11-11') - 2; -- 20111128
select * from emp where last_day(hiredate) -2 = hiredate;
-- (10)Identify employees who were hired earlier than 12 years ago(I.e. more than 12 years of employment)
select * from emp where date_add(hiredate,interval 12 year) < now();
-- (11)Displays the names of all employees in lowercase
select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) from emp;
-- (12)Displays the name of an employee with exactly 5 characters
select * from emp where length(ename) = 5;
(13) Displays the names of employees without an "R"
(14) Displays the first three characters of all employee names
(15) Display the names of all employee posts and replace all 'a' with a
(16) Displays the name and date of employment of employees who have completed 10 years of service
(17) Displays employee details, sorted by name
(18) Displays the employee's name and date of employment, and ranks the oldest employee first according to their years of service
(19) Displays the names, work and salary of all employees, sorted by work in descending order. If the work is the same, sorted by salary
(20) Displays the names of all employees, the year and month of joining the company, sorted by the month of employment date. If the month is the same, the employees of the earliest year will be listed first
(21) show the daily salary of all employees when a month is 30 days, and ignore the balance
(22) find out all employees employed in February (of any year)
(23) for each employee, the number of days he / she joined the company is displayed
(24) display the names of all employees with 'A' anywhere in the name field
(25) display the service years of all employees in the form of month, year and day
-- (13)Display without "R" Name of employee select * from emp where ename not like '%R%'; -- (14)Displays the first three characters of all employee names select left(ename,3) from emp; -- (15)Display the names of all employees, in a Replace all'A' select replace(ename, 'A', 'a') from emp; -- (16)Displays the name and date of employment of employees who have completed 10 years of service select ename, hiredate from emp where date_add(hiredate, interval 10 year) <= now(); -- (17)Displays employee details, sorted by name select * from emp order by ename asc; -- (18)Displays the employee's name and date of employment, and ranks the oldest employee first according to their years of service select ename, hiredate from emp order by hiredate asc; -- (19)Displays the names, work and salary of all employees, sorted by work in descending order. If the work is the same, sorted by salary select ename, job, sal from emp order by job desc,sal; -- (20)Displays the names of all employees, the year and month of joining the company, sorted by the month of employment date, -- If the months are the same, the employees of the earliest year will be ranked first select ename, concat(year(hiredate),'-',month(hiredate)) from emp order by month(hiredate) asc, year(hiredate) asc; -- (21)Show the daily salary of all employees when a month is 30 days, ignoring the balance select ename, floor(sal/30), sal/30 from emp; -- (22)Find out all employees hired in February (any month of the year) select * from emp where month(hiredate) = 2; -- (23)For each employee, the number of days he / she joined the company is displayed select ename, datediff(now(), hiredate) from emp; -- (24)Displays any location in the name field that contains'A'Names of all employees select * from emp where ename like '%A%'; -- (25)The service years of all employees are displayed in the form of month, year and day -- Idea: 1. Find out how many days you worked first select ename, datediff(now(), hiredate) from emp; -- 2. Convert days to months select ename, floor(datediff(now(), hiredate) / 365) as " Year of work", floor(datediff(now(), hiredate) % 365 / 30 ) as " Month of work", datediff(now(), hiredate) % 31 as " Working days" from emp;
7. Write the correct sql according to: emp employee table, dept department table, salary = salary sal + commission comm
(1) List all departments with at least one employee
(2) List employee information with more salary than "SMITH"
(3) List all employees whose employment date is later than their direct supervisor
(4) List Department names and employee information of employees in these departments, as well as those departments without employees
(5) List the names of all clerks and their departments
(6) List jobs with a minimum salary of more than 1500
(7) List the names of employees working in the Department "SALES"
(8) List all employees whose salary is higher than the average salary of the company
-- (1)List all departments with at least one employee -- Idea: first query how many people in each department use it having Clause filtering select count(*) as c, deptno from emp group by deptno having c > 1; -- (2)Column salary ratio“ SMITH" More employee information -- Find out first SMITH of sal (Subquery)，Then other employees sal > SMITH that will do select * from emp where sal > (select sal from emp where ename = 'SMITH'); -- (3)List all employees whose employment date is later than their direct supervisor /* First, treat the emp table as two tables: worker and leader Condition 1 worker. hiredate > leader. hiredate 2. worker.mger = leader.empno */ select worker.ename as "Employee name", worker.hiredate as "Employee induction time", leader.ename as "Superior name", leader.hiredate as "Superior employment time" from emp worker, emp leader where worker.hiredate > leader.hiredate and worker.mgr = leader.empno; -- (4)List Department names and employee information of employees in these departments, as well as those departments without employees /* Because the user department needs to be displayed here, the external connection (left external connection) is considered */ select dname, emp.* from dept left join emp on dept.deptno = emp.deptno; -- (5)List all“ CLERK" (The name of the clerk and the name of his department select ename, dname, job from emp, dept where emp.deptno = dept.deptno and job = 'CLERK'; -- (6)List jobs with a minimum salary of more than 1500 /* Query the minimum wage of each department Filtering with having clause */ select min(sal) as min_sal, job from emp group by job having min_sal > 1500; -- (7)List in Department"SALES"(Name of the employee working in the sales department) select ename, dname from emp, dept where emp.deptno = dept.deptno and dname = 'SALES'; -- (8)List all employees whose salary is higher than the average salary of the company select * from emp where sal > (select avg(sal) from emp);
(9) List all "SCOTT" employees who do the same job
(10) List the names and salaries of all employees whose salary is higher than that of their department 30
(11) List the number of employees working in each department, average salary and average length of service
(12) List the names, Department names and salaries of all employees
(13) List the details and number of people in all departments
(14) List the minimum wage for various jobs
(15) List the minimum salary of manager
(16) List the annual salary of all employees, sorted from the lowest to the lowest
-- (9)Listed in“ SCOTT" All employees engaged in the same work select * from emp where job = (select job from emp where ename = 'SCOTT'); -- (10)List the names and salaries of all employees whose salary is higher than that of their department 30 -- First find out the maximum salary of department No. 30 select ename, sal from emp where sal > (select max(sal) from emp where deptno = 30); -- (11)List the number of employees working in each department, average salary and average length of service(Time unit) select count(*) as "Number of department employees", deptno, avg(sal) as "Department average wage", format(avg(datediff(now(), hiredate) / 365),2) as "Average service life(year)" from emp group by deptno; -- (12)List the names, Department names and salaries of all employees -- namely emp and dept Joint query, connection condition emp.deptno = dept.deptno select ename, dname, sal from emp, dept where emp.deptno = dept.deptno; -- (13)List the details and number of people in all departments -- 1. Get the number of people in each department first,Consider the following results as a temporary table select count(*) as c, deptno from emp group by deptno; -- 2. and dept Table union query select dept.*, tmp.c as "Number of departments" from dept, (select count(*) as c, deptno from emp group by deptno) tmp where dept.deptno = tmp.deptno; -- (14)List the minimum wage for various jobs select min(sal), job from emp group by job; -- (15)list MANAGER(manager)Minimum salary select min(sal), job from emp where job = 'MANAGER'; -- (16)List the annual salary of all employees, sorted from the lowest to the lowest -- 1. Get the employee's annual salary first select ename, (sal + ifnull(comm, 0)) * 12 year_sal from emp order by year_sal asc;
8. The school environment is as follows: a department has several majors, each major recruits only one class a year, and each class has several students.
Now it is necessary to establish a database related to departments, students and classes. The relationship mode is:
CLASS name, CLASS name, CLASS name
Student (Student ID, name, age, class ID)
DEPARTMENT (DEPARTMENT number, DEPARTMENT ID, DEPARTMENT name, deptname)
Try SQL language to complete the following functions:
(1) To create a table, it is required to declare in the definition: 1. The main foreign key of each table; 2. deptname is a unique constraint; 3. The student name cannot be empty
(2) Insert data as follows:
DEPARTMENT( 001，mathematics; 002,，computer; 003，Chemistry; 004，chinese; 005，Economics ) CLASS( 101，Software, computer, 1995, 20; 102，Microelectronics, computer, 1996, 30; 111，Inorganic chemistry, chemistry, 1995, 29; 112，Polymer chemistry, chemistry, 1996, 25; 121，Statistical mathematics, mathematics, 1995, 20; 131，Modern language, Chinese, 1996, 20; 141，International trade, economics, 1997, 30; 142，International finance, economics, 1996, 14 ) STUDENT( 8101，Zhang San, 18101; 8102，Qian Si, 16121; 8103，Wang Ling, 17131; 8105，Li Fei, 19102; 8109，Zhao Si, 18141; 8110，Li Ke, 20142; 8201，Zhang Fei, 18111; 8302，Zhou Yu, 16112; 8203，Wang Liang, 17111; 8305，Dong Qing, 19102; 8409，Zhao long, 18101; 8501. Li Li, 20142; )
(3) Complete the following query functions
(3.1) find out all the students who have sex with Li
(3.2) list the names of all departments with more than one major
(3.3) list the number and name of the Department with 30 or more people
(4) The school has added a new physics department, numbered 006
(5) Student Zhang San dropped out of school. Please fill in the relevant form
-- Create class CLASS surface
create table `class`(
classid int primary key,
`subject` varchar(32) not null default '',
deptname varchar(32), -- Foreign key field, specified after table definition
enrolltime int not null default 1990,
num int not null default 0,
foreign key (deptname) references DEPARTMENT(departname)
);
-- Create students STUDENT surface
create table zzp_student(
studentid int primary key,
`name` varchar(32) not null default '',
age int not null default 0,
classid int, -- Foreign key
foreign key (classid) references `class`(classid)
);
-- Create system DEPARTMENT surface
create table DEPARTMENT(
departmanetid varchar(32) primary key,
departname varchar(32) unique not null
);
-- add to DEPARTMENT Table data
insert into DEPARTMENT values ('001','mathematics');
insert into DEPARTMENT values ('002','computer');
insert into DEPARTMENT values ('003','Chemistry');
insert into DEPARTMENT values ('004','chinese');
insert into DEPARTMENT values ('005','Economics');
select * from DEPARTMENT;
-- add to class Table data
insert into `class` values (101,'Software','computer',1995,20);
insert into `class` values (102,'microelectronics','computer',1996,30);
insert into `class` values (111,'inorganic chemistry','Chemistry',1995,29);
insert into `class` values (112,'Polymer chemistry','Chemistry',1996,25);
insert into `class` values (121,'Statistical Mathematics','mathematics',1995,20);
insert into `class` values (131,'modern languages','chinese',1996,20);
insert into `class` values (141,'international trade','Economics',1997,30);
insert into `class` values (142,'international finance','Economics',1996,14);
select * from `class`;
-- add to zzp_student Table data
insert into zzp_student values (8101,'Zhang San',18,101);
insert into zzp_student values (8102,'Qian Si',16,121);
insert into zzp_student values (8103,'Wang Ling',17,131);
insert into zzp_student values (8105,'Li Fei',19,102);
insert into zzp_student values (8109,'Zhao Si',18,141);
insert into zzp_student values (8110,'Li Ke',20,142);
insert into zzp_student values (8201,'Fei Zhang',18,111);
insert into zzp_student values (8302,'Zhou Yu',16,112);
insert into zzp_student values (8203,'Liang Wang',17,111);
insert into zzp_student values (8305,'Dong Qing',19,102);
insert into zzp_student values (8409,'Zhao Long',18,101);
insert into zzp_student values (8501,'Li Li',20,142);
select * from zzp_student;
-- (3)Complete the following query functions
-- (3.1)Find out all the students who have sex with Li
-- query zzp_student Table, using like
select * from zzp_student where `name` like "%Lee%";
-- (3.2)List the names of all departments with more than one major
-- 1. First, check how many majors there are in each department
select count(*) as nums, deptname from `class` group by deptname having nums > 1;
-- (3.3)List the numbers and names of departments with 30 or more people
-- 1. First find out how many people there are in each department and get >= 30 Department of
select sum(num) as nums, deptname from `class` group by deptname having nums >= 30;
-- 2. Think of the above results as a temporary table and department Joint query is enough
select tmp.*, department.departmanetid from department,
(select sum(num) as nums, deptname
from `class` group by deptname having nums >= 30) as tmp
where department.departname = tmp.deptname;
-- (4)The school has added a new physics department, numbered 006
-- Add a piece of data
insert into DEPARTMENT values ('006','physics department');
-- (5)Student Zhang San dropped out of school. Please fill in the relevant form
-- Analysis: 1. Number of Zhang San's class-1 2. Delete Zhang San from the student list
-- 3. Transaction control is required
-- Open transaction
start transaction;
-- Number of Zhang San's class-1
update `class` set num = num - 1
where classid = (select classid from zzp_student where `name` = 'Zhang San');
-- Delete Zhang San from the student list
delete from zzp_student where `name` = 'Zhang San';
-- Commit transaction
commit;