MySQL database management

MySQL database management

1, the basic information view
Mysql> select version ();
Mysql> Select Database ();
Mysql> Show variables like '%character% ';
mysql> set names UTF8;
Mysql> show engines;
Mysql> Show create TABLE test;
Mysql> Show CREATE Database sxjy;
DESC test;
2. Create a database
Mysql> CREATE DATABASE sxjy default character set UTF8;
Mysql>set Char set ' GPK ';
mysql> show databases;
mysql> drop Database Sxjy;

3. Create a data table
mysql> use SXJY;
Mysql> CREATE TABLE Stu_info (ID int,sno varchar), name varchar (+), sex enum (' Male ', ' female '), age Int,phone varchar (15), Addr varchar (60));
Mysql> Show tables;
mysql> drop table Stu_info;

Mysql> CREATE TABLE stu_info (ID int primary KEY AUTO_INCREMENT,SNO varchar (TEN) not null,name varchar (+) not null,sex E Num (' Male ', ' female '), age int,phone varchar (addr), auto_increment=1;


4. Self-increment setting of table
Mysql> Show variables like ' auto_increment% ';
| auto_increment_increment | 1 |
| Auto_increment_offset | 1 |

Auto_increment_increment the value of each increment in the field
Auto_increment_offset The starting value of the self-increment field

5. mysql Data type

(1) Date and time type
Date 3 bytes, format: year-month-day, 2015-07-15
Time 3 bytes, format: Hours: minute: seconds, 21:30:20
DateTime 8 Bytes, format: year-month-day time: minute: sec 2015-07-15 21:30:20
Timestamp 4 bytes, automatically store record modification time
Year 1 bytes, years stored

Example: Create a data table containing the above field types
Mysql> CREATE TABLE test (ID int primary key,name varchar), birthday date,birthtime time,birthdt datetime,birthyear y Ear,record timestamp);

(2) integer type
Tinyint 1 bytes, range ( -128-->127)
SmallInt 2 bytes, range ( -32768-->32767)
Mediumint 3 bytes, range ( -8388608-->8388607)
int 4 byte, range ( -2147483648-->2147483647)
BigInt 8 bytes, range (+-9.22*10 18-square)
If declared as unsigned type, the value is 0-->2 N (word length) 1

(3) floating-point type
Float (n,m) 4 bytes, single-precision floating point type (n = total number of bits, M represents decimal place)
Double (n,m) 8 bytes, dual precision floating point type (n = total number of digits, M for decimal digits)
Decimal (N,M) A floating-point number stored as a string

Exercise: Create a table that contains a1,a2,a3 three columns, declared as 3 types above, specify an n value of 5,m value of 3, then insert 123.45678, and then use the Select query to display the value of what?

(4) String data type
char (n) fixed length, up to 255 characters
varchar (n) variable length, up to 65,565 characters
Tinytext variable length, up to 255 characters
Text variable length, up to 65,535 characters
Mediumtext variable length, up to 2 of 24 square-1 characters
Longtext variable length, up to 2 of 32 square-1 characters

(5) Blog data type (binary storage type)
Tinyblob Max 255 bytes
Blob Max 65K bytes
Mediumblob Maximum 16M bytes
Longblob Max 4GB

(6) Other types
enum enum type, enum (' Man ', ' woman ')
Set is a list of 0 or more optional values in the data, up to 64 members, Favorate (' Basketball ', ' Football ', ' table tennis ', ' baseball ')

(7) Properties of the data type
Auto_increment
Binary is only used for char and varchar columns, and is sorted in case-enabled manner
Default setting defaults, and columns must be constants; Enum (' Man ', ' woman ') is not null default ' man '
Index index, index index name (column name) ===== There are four kinds of indexes
Primary key primary key index, the value must be unique and cannot be empty, a table can have only one primary key column
Not NULL is not allowed as a null value
Unique unique index, can be allowed to be null, can set multiple columns

Exercise: Create a data table that contains a primary key, a unique index, a set-type column, not a null column
INSERT into test (name) VALUES (' John Doe ');
SELECT * FROM Test
SET @ @auto_increment_increment = 5;

6, modify the table ALTER TABLE

(1) Adding columns
mysql> ALTER TABLE ADD column age int; =====alter table Test Add column Cnum int (2) Zerofill;
Add by default on the last side
mysql> desc test;
Mysql> ALTER TABLE test add column sex enum (' Man ', ' woman ') is not null by default ' man ' after name;
ALTER TABLE test Add column phone varchar (a) not NULL after name;

Mysql> ALTER TABLE Test Add column sno varchar (ten) first;

(2) Delete a column
mysql> ALTER TABLE test drop ID;
mysql> ALTER TABLE test drop column age;
ALTER TABLE test drop phone;

(3) modifying columns
Mysql> ALTER TABLE test modify age tinyint not null; ===== Modifying the type of a column
ALTER TABLE test modify Cnum tinyint;
ALTER TABLE test Modify Cnum tinyint (1) zerofill unsigned;
mysql> ALTER TABLE test change name sname varchar (+) not null;

ALTER TABLE test change cnum cnumber tinyint (1) zerofill unsigned NOT null;
===============
Invalid Use of NULL value

(4) Renaming a table
mysql> ALTER TABLE test Rename to MyTest;
Mysql> Rename table mytest to test;

7. Constraints
(1) PRIMARY KEY constraint (primary key)
(2) Unique constraint (unique)
(3) Non-null constraint (NOT NULL)
(4) FOREIGN KEY constraint
Mysql> CREATE TABLE sinfo (ID int primary key AUTO_INCREMENT,SNO varchar () NOT NULL unique,name varchar (a) Not null,c No varchar (ten), age tinyint unsigned,addr varchar, foreign key (CNO) references Class (CNO)) auto_increment=1,engine= InnoDB

Mysql> ALTER TABLE Stu_info add constraint CNO_FK foreign key (CNO) references Class (CNO) on delete restrict on update r Estrict;
On DELETE cascade: Cascading updates and deletions
On delete set NULL: UPDATE or delete the corresponding record of the child table to null
On delete restrict: Delete/update operation is not allowed if the child table has a matching record
mysql> ALTER TABLE sinfo drop foreign key sinfo_ibfk_1;

8. mysql Statement type

(1) DDL statements
Create Database
Drop database
CREATE table
ALTER TABLE
drop table
CREATE index
Drop Index
(2) DML statements
Insert (inserting data)
mysql> INSERT INTO Sinfo values (2, ' s000002 ', ' Chaofang ', ' c0002 ', 33, ' Hunan province Changsha ');
mysql> INSERT INTO Sinfo set id=3,sno= ' s000003 ', Name= ' Zhang because ', cno= ' c0003 ', age=36,addr= "Changsha in Hunan province set Wang Tai";
mysql> INSERT INTO Sinfo select Id,sno,name,cno,age,addr from Stu_info;

Update (updating data)
mysql> Update sinfo set age=48 where id=4;

Delete (remove data)
Mysql> Delete from Sinfo where id>4;

Tuncate (empty table)
mysql> truncate sinfo;

Select (data query)

Where Condition expression:
and logic with salary > salary<10000
or logic or salary < salary>10000
Not logic is not a sex= "man"
Between salary between 10000

= equals
> Greater than
< less than
>= greater than or equal to
<= less than or equal to
! = does not equal
<> Not equal to

+,-, *,/Add, subtract, multiply, divide

Is null value is empty
The like character matches the name like '%apple% '
Group BY and having group and conditional settings
Limit specifies the number of outputs for a record

Order By field name ASC | DESC
Mysql-u ROOT-PAIXOCM < Employees.sql
mysql-uroot-p123456
Use books
SOURCE D:/bookorama.sql

SELECT * from salaries where salary>=60000 and salary<=70000;

SELECT * from salaries where salary between 60000 and 70000;

SELECT * FROM employees where first_name like '%mary% ';

SELECT * FROM employees where first_name are not null;

SELECT * from salaries where salary in (50000,60000,70000);
= equals

Mysql> SELECT * from salaries where salary in (50000,60000,70000) order by Emp_no ASC;
Mysql> SELECT * from salaries where salary in (50000,60000,70000) Order by emp_no Desc;

Exercise: Count the number of employees born per year from 1960 (including 1960) in the Employees table

Mysql> Select Year (birth_date) as date of birth, count (year (birth_date)) as number of years of birth from employees group by birth year >=1960;

Mysql> Select Year (birth_date) as Birth date, count (year (birth_date) as number of persons from employees group by birth year have not born year >=1 960;
function =====http://10.0.0.254/functions.html#func-op-summary-ref

Example: The average wage for employees, greater than the average wage and the number of people less than average
Mysql> Select AVG (Salary) as average wage from salaries;
Select COUNT (*) as is less than average wage, avg (salary) as average salary from salaries where salary< (select AVG (Salary) from salaries);
http://blog.csdn.net/wwxtu24/article/details/3974444


9. Multi-Table Query
Check the numbers, names, grades and addresses of all students
Mysql> Select Infoa.sno,infoa.name,infoa.score,infob.addr from Infoa,infob where Infoa.sno=infob.sno;

INNER JOIN: Fetch the intersection of all query tables
Select Infoa.sno,infoa.name,infoa.score,infob.addr from Infoa inner join,infob where Infoa.sno=infob.sno;

where=====
on=======

Left join: The intersection of all the records from the table to the right
Right join: The intersection of all the records of the side table and the left table

Exercise: Find all student numbers, names, grades and addresses that reside in Changsha

Mysql> Select Infoa.sno,infoa.name,infoa.score,infob.addr from Infoa left joins Infob on Infoa.sno=infob.sno where addr = "Changsha of Hunan province";

Mysql> Select A.sno,a.name,a.score,b.addr from Infoa as a left joins Infob as B on A.sno=b.sno where addr= "Hunan province Changsha";


Exercise: Query the number of employees in each department in the employees database, and then give the name and number of departments

Exercise: Find out the number of men and women
Mysql> Select Gender Gender, COUNT (*) as number from employees group by gender;

Exercise: Check the average wage of men and women
Mysql> Select Employees.gender,avg (salaries.salary) from Employees,salaries where Employees.emp_no=salaries.emp_ No GROUP by gender;


Exercise: List The average wage for each department, sorted in descending order
Mysql> Select Dept_emp.dept_no as Sector number, AVG (salaries.salary) as average salary from dept_emp inner join salaries on Dept_emp.emp_n O=salaries.emp_no GROUP by dept_no the average wage desc;

Mysql> Select A.dept_name,b.dept_no,avg (c.salary) as average salary from departments a,dept_emp b,salaries C where a.dept_no=b.d Ept_no and B.emp_no=c.emp_no GROUP by Dept_no order by average salary desc;

Mysql> Select A.dept_name as Sector name, B.dept_no as department number, AVG (c.salary) as average salary from departments a,dept_emp b,salaries C wher e A.dept_no=b.dept_no and B.emp_no=c.emp_no Group by B.dept_no order by average salary desc;

Mysql> Select A.dept_name as Sector name, B.dept_no as department number, AVG (c.salary) as average salary, Max (c.salary) as maximum wage, min (c.salary) as minimum wage f ROM Departments a,dept_emp b,salaries C where a.dept_no=b.dept_no and B.emp_no=c.emp_no group by B.dept_no order by average salary D Esc

Exercise: List The department name, department number, department manager name (including First_Name and last_name), and gender for each department

Mysql> Select A.dept_no as department number, a.dept_name as department name, Concat (C.first_name, ', C.last_name) as department manager, C.gender as gender from D Epartments A,dept_manager b,employees C where a.dept_no=b.dept_no and B.emp_no=c.emp_no order by department number;

Exercise: Information about employee number, employee name (first_name+last_name), department, employee and salary of 1990 years after joining the company
Create a new table Einfo.

Mysql> CREATE TABLE Einfo (select Employees.emp_no,concat (Employees.first_name, ', Employees.last_name) as employee name, Employees.hire_date,departments.dept_name,salaries.salary from employees left join (DEPARTMENTS,SALARIES,DEPT_EMP) On Employees.emp_no=salaries.emp_no and Dept_emp.dept_no=departments.dept_no and Employees.emp_no=dept_emp.emp_no Where year (employees.hire_date) >=1990);

This article is from the "Kenasel" blog, make sure to keep this source http://kenasel.blog.51cto.com/10620829/1839490

MySQL database management