MySQl database must know SQL statements (enhanced version), mysqlsql

MySQl database must know SQL statements (enhanced version), mysqlsql

This is an enhanced version. The problem and SQL statement are as follows.

Create a users table and set the id, name, gender, and sal fields. id indicates the primary key.

Drop table if exists users; create table if not exists users (id int (5) primary key auto_increment, name varchar (10) unique not null, gender varchar (1) not null, sal int (5) not null); insert into users (name, gender, sal) values ('A', 'male', 1000); insert into users (name, gender, sal) values ('bb', 'female, 1200 );

Bytes --------------------------------------------------------------------------------------

One-to-one: what is the ID number of AA?

Drop table if exists users; create table if not exists users (id int (5) primary key auto_increment, name varchar (10) unique not null, gender varchar (1) not null, sal int (5) not null); insert into users (name, gender, sal) values ('A', 'male', 1000); insert into users (name, gender, sal) values ('bb ', 'female', 1200); drop table if exists cards; create table if not exists cards (id int (5) primary key auto_increment, num int (3) not null unique, loc varchar (10) not null, uid int (5) not null unique, constraint uid_fk foreign key (uid) references users (id )); insert into cards (num, loc, uid) values (111, 'beijing', 1); insert into cards (num, loc, uid) values (222, 'shanghai ', 2 );

[Note: inner join indicates inner join]

Select u. name "name", c. num "ID number" from users u inner join cards c on u. id = c. uid where u. name = 'a'; -- select u. name "name", c. num "ID number" from users u inner join cards c on u. id = c. uid where name = 'a ';

---------------------------------------------

One-to-many: Query employees in the "Development Department"

Create a groups table

Drop table if exists groups; create table if not exists groups (id int (5) primary key auto_increment, name varchar (10) not null); insert into groups (name) values ('date'); insert into groups (name) values ('salesman ');

Create an emps table

Drop table if exists emps; create table if not exists emps (id int (5) primary key auto_increment, name varchar (10) not null, gid int (5) not null, constraint gid_fk foreign key (gid) references groups (id); insert into emps (name, gid) values ('haha ', 1); insert into emps (name, gid) values ('hashes ', 1); insert into emps (name, gid) values ('giggle', 2); insert into emps (name, gid) values ('dump ', 2 );

Query the employees of the "Development Department"

Select g. name "department", e. name "employee" from groups g inner join emps e on g. id = e. gid where g. name = 'dashboard '; -- select g. name "department", e. name "employee" from groups g inner join emps e on g. id = e. gid where g. name = 'dashboard ';

------------------------------------------------------

Many-to-many: query the students "Zhao" taught

Create a students table

Drop table if exists students; create table if not exists students (id int (5) primary key auto_increment, name varchar (10) not null); insert into students (name) values ('haha '); insert into students (name) values ('giggle ');

Create a teachers table

Drop table if exists teachers; create table if not exists teachers (id int (5) primary key auto_increment, name varchar (10) not null); insert into teachers (name) values ('zhao '); insert into teachers (name) values ('Liu ');

Create the primary key (sid, tid) in the middles table to join the primary key. The two fields must be unique.

drop table if exists middles; create table if not exists middles(   sid int(5),   constraint sid_fk foreign key(sid) references students(id),   tid int(5),   constraint tid_fk foreign key(tid) references teachers(id),   primary key(sid,tid)  ); insert into middles(sid,tid) values(1,1); insert into middles(sid,tid) values(1,2); insert into middles(sid,tid) values(2,1); insert into middles(sid,tid) values(2,2); 

Query the Students taught by Zhao

Select t. name "instructor", s. name "student" from students s inner join middles m inner join teachers t on (s. id = m. sid) and (m. tid = t. id) where t. name = 'zhao '; -- select t. name "instructor", s. name "student" from students s inner join middles m inner join teachers t on (s. id = m. sid) and (t. id = m. tid) where t. name = "Zhao ";

Bytes --------------------------------------------------------------------------------------------------------

Mark employees above 5000 yuan (inclusive) as "high salaries"; otherwise, they are marked as "Starting Salaries"

Identify an employee whose salary is NULL as "unpaid"

Mark employees above 5000 yuan (inclusive) as "high salaries"; otherwise, they are marked as "Starting Salaries"

Mark an employee of 7000 yuan as "high salary", an employee of 6000 yuan as "medium salary", and an employee of 5000 yuan as "starting salary". Otherwise, the employee is marked as "trial salary"

Bytes ---------------------------------------------------------------------------------------------------------

Internal Connection (equivalent connection): query the customer name, order number, and order price

[Note: MERs c inner join orders o uses an alias, and then o Represents orders]

Select c. name "customer name", o. isbn "Order No.", o. price "Order price" from customers c inner join orders o on c. id = o. customers_id; -- select c. name "customer name", o. isbn "Order No.", o. price "Order price" from customers c inner join orsers o on c. id = o. customers_id;

On + two table join conditions. One table's primary key and one table's foreign key

Inner join: only two records in the table can be queried Based on the connection conditions, which is similar to the intersection in mathematics.

----------------------------------------------------

External Connection: queries the name and number of orders of each customer by customer group.

Outer Join: You can query the records in both tables based on the connection conditions, and forcibly query records that are not satisfied with the conditions of the brother.

External connections can be subdivided:

<Left outer join: Take the left side as the reference, and left outer join indicates select c. name, count (o. isbn) from MERs c left outer join orders o on c. id = o. customers_id group by c. name; --> right outer join: select c. name, count (o. isbn) from orders o right outer join MERs c on c. id = o. customers_id group by c. name;

Left outer join indicates that all content on the left will be displayed. For example, customers c left out join indicates that all content in a column of customers will be found.

------------------------------------------------------
Self-connection: the boss of AA is EE. Think of yourself as two tables. One left and one right

select users.ename,bosss.ename from emps users inner join emps bosss on users.mgr = bosss.empno; select users.ename,bosss.ename from emps users left outer join emps bosss on users.mgr = bosss.empno; 

Bytes -----------------------------------------------------------------------------------------------
Demonstrate functions in MySQL (query manual)

Date and time functions:

Select addtime ('2014-8-7 23:23:23 ', '1: 1: 1'); time addition select current_date (); select current_time (); select now (); select year (now (); select month (now (); select day (now (); select datediff ('2017-12-31 ', now ());

String functions:

Select charset ('hahaha'); select concat (' ', 'hahaha',' '); select instr ('www .baidu.com', 'baidu '); select substring ('www .baidu.com ', 5, 3 );

Mathematical functions:

Select bin (10); select floor (3.14); // The maximum integer smaller than 3.14 --- positive 3 select floor (-3.14 ); // The maximum integer smaller than-3.14 --- negative 4 select ceiling (3.14); // The minimum integer greater than 3.14 --- positive 4 select ceiling (-3.14 ); // The smallest integer greater than-3.14-negative 3, which must be an integer select format (3.1415926, 3). The value is rounded to select mod () after the decimal point ); // select rand () from the remainder ();//

Encryption function:

Select md5 ('200 ');

Returns the 32-bit hexadecimal number e10adc3949ba59abbe56e057f20f883e.

Demonstrate MySQL process control statements

Use json; drop table if exists users; create table if not exists users (id int (5) primary key auto_increment, name varchar (10) not null unique, sal int (5); insert into users (name, sal) values ('haha ', 3000); insert into users (name, sal) values ('haha ', 4000); insert into users (name, sal) values ('giggle, 5000); insert into users (name, sal) values ('dump', 6000 ); insert into users (name, sal) values ('ming', 7000); insert into users (name, sal) values ('silies', 8000); insert into users (name, sal) values ('jun Jun ', 9000); insert into users (name, sal) values ('zhao Zhao', 10000); insert into users (name, sal) values ('nameless ', NULL );

Mark employees above 5000 yuan (inclusive) as "high salaries"; otherwise, they are marked as "Starting Salaries"

Select name "name", sal "salary", if (sal> = 5000, "high salary", "starting salary") "Description" from users;

Identify an employee whose salary is NULL as "unpaid"

Select name "name", ifnull (sal, "no salary") "salary" from users;

Mark employees above 5000 yuan (inclusive) as "high salaries"; otherwise, they are marked as "Starting Salaries"

Select name "name", sal "salary", case when sal> = 5000 then "high salary" else "starting salary" end "Description" from users;

Mark an employee of 7000 yuan as "high salary", an employee of 6000 yuan as "medium salary", and an employee of 5000 yuan as "starting salary". Otherwise, the employee is marked as "trial salary"

Select name "name", sal "salary ", case sal when 3000 then "" when 4000 then "" when 5000 then "trial salary" when 6000 then "" when 7000 then "" when 8000 then "Yes salary "when 9000 then" high salary "else" heavy salary "end" Description "from users;

The above is the MySQl database that I will introduce to you in small series. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!