MySQL database CRUD statement quick start _ MySQL

MySQL database CRUD statement quick start bitsCN.com
MySQL database CRUD statement quick start SQL statement SQL statement, a language database used to operate the database, simply understood as a file on the hard disk, SQL statements that can store data can be classified into two types of databases: 1. command line code: enter database C: /mysql-hlocalhost-P3306-uroot-p123456 start database net start mysql stop database net stop mysql 1. operations on database and table 1. create for common operations on database and table view show modify alter delete drop // create database mydb; // view all database libraries show databases; // delete mydb drop database mydb; // delete the user table drop table user; Insert, delete, update, query, select 2, create a database named mydb1, create database mydb1, and create a mydb2 database using the UTF-8 character set. Create database mydb2 character set utf8; create a mydb3 database that uses the UTF-8 character set and has verification rules. Create database mydb3 character set utf8 collate utf8_general_ci; view all databases in the current database server show databases; view the definition information of the mydb2 database created earlier, character information show create database mydb2; delete the mydb3 database created earlier, drop database mydb3, and change the character set of mydb2 to gbk alter database mydb2 character set gbk; // back up the database use mydb2; create table a (name varchar (20); // insert data into a (name) values ("zhangsan"); insert into a (name) values ("wangwu"); // back up data Database mysqldump-uroot-proot mydb1> d:/a. SQL is similar to mysql, so it cannot be used in mysql. // Delete mydb2 drop database mydb2; // restore database create database mydb22; source d:/. SQL // source command to specify an SQL script to close the error message Mysql-uroot-p123456-B 3. operation table 1. create table create employee table Id shaping name complex type sex regular type or bit brithday date type Entry_date date type job complex type Salary decimal type resume large text type create table: create table employee (id int, name varchar (20), gender varchar (6), brithday date, entry_date date, job varchar (20), salary double, reshme text); 2. modify On the basis of the preceding employee table, the table adds an image column alter table employee add image blob; modifies the job column so that its length is 60 alter table employee modify job varchar (60 ); delete the sex column. Alter table employee drop sex; change table name to users. Rename table employee to user; modify the character set of the table to UTF-8 alter table user character set utf8; change the column name to username alter table user change column name username varchar (20 ); view all tables show tables; view the table creation statement show create table user; view the table structure desc user; delete the user table drop table user; 4. add, delete, modify, and query data in the table. There are four data operations in the table: add, delete, modify, and query crud create 1. insert statement employee. SQL create table employee (id int, name varchar (20), sex varchar (10), birthday Date, salary float, entry_date date, resume text); // Insert three records into the employee Table: Insert into employee (id, name, sex, birthday, salary, entry_date, resume) values (1, 'hangsan', 'male', '2017-11-23 ', 1987, '2017-2-18', 'Good boy '); insert into employee (id, name, sex, birthday, salary, entry_date, resume) values (2, 'wangwu', 'male', '2017-11-23 ', 1988, '2017-2-18 ', 'Good boy '); insert into employee (id, name, sex, birthday, salary, entry_date, Resume) values (3, 'xiaohong ', 'female', '2017-11-23 ', 1983, '2017-2-18', 'Good girl '); insert into employee (id, name, sex, birthday, salary, entry_date, resume) values (4, 'Zhao nan', 'male', '2017-11-23 ', 1986, '2017-2-18 ', 'Super good guy '); // view all records in the table select * from employee; // view all codes used by the data show variables like 'character % '; // modify the client encoding to gbk set character_set_client = gbk; // modify the result set encoding to gbk set character_set_results = gbk; // insert statement Create table a (username varchar (20), password varchar (20); insert into a values ('zs', '123 '); insert into a values ('wangw', '000000'), ('lisi', '000000'); insert a values ('AAA', 'BBB '); insert a (username) values ('aaa'); 2. the update statement changes the salary of all employees to 5000 yuan update employee set salary = 5000, and the salary of employees named 'zhangsan' to 3000 yuan. Update employee set salary = 3000 where name = 'hangsan'; change the employee's salary of 'lisi' to 4000 yuan, and change sex to female. Update employee set salary = 4000, sex = 'female 'where name = 'wangwu '; increase xiaohong's salary by 1000 RMB on the original basis. update employee set salary = salary + 1000 where name = 'xiaohong '; 3. delete statement: delete the record whose name is "Zhao Nan. Delete from employee where name = 'zhaonan '; delete all records in the table. Delete is the deletion of multiple entries. if there are many entries, the efficiency will be very low: delete from employee; use truncate to Delete records in the table. Destroy the table and create a table. try to use the truncate employee. 4. select statement student. SQL create table student (id int, name varchar (20), chinese float, english float, math float); insert into student (id, name, chinese, english, math) values (1, 'Zhang Xiaoming ', 90); insert into student (id, name, chinese, english, math) values (2, 'Li Jin', 67,53, 95 ); insert into student (id, name, chinese, english, math) values (3, 'Wang Wu', 87,78, 77); insert into student (id, na Me, chinese, english, math) values (4, 'Li yi', 88,98, 92); insert into student (id, name, chinese, english, math) values (5, 'lilaicai ', 67); insert into student (id, name, chinese, english, math) values (6, 'zhangjinbao', 45 ); insert into student (id, name, chinese, english, math) values (7, 'huangrong ', 30); query information of all students in the table. Select * from student; query the names and English scores of all students in the table. Select name, english from student; filter duplicate data in the table. Select distinct english from student; add 10 points to all student scores. Select name, english + 10, chinese + 10, math + 10 from student; calculate the total score of each student. Select name, english + chinese + math as sum from student; use aliases to indicate student scores. Select * from student where name = 'Li Yi'; select * from students where english> 90; select name, english + chinese + math sum from student where english + chinese + math> 200; you can use the as operator to query students with English scores between 80 and 90. Select * from student where english between 65 and 85; query students whose mathematical scores are, 90, and 91. Select name, math from student where math in (, 90, 91); query the scores of all students surnamed Li. Select * from student where name like 'Li % '; // select * from students whose name is Li * from student where name like 'Li _'; query math score> 80, students with Chinese scores greater than 80. Select * from student where math> 80 and chinese> 80; query select * for students with English> 80 or total score> 200 *, chinese + math + english from student where english> 80 or chinese + english + math> 200; order by clause sorts mathematical scores and outputs them. Select * from student order by math; sort the total score and then output select * in the order from high to low *, chinese + math + english from student order by chinese + math + english desc; the order clause output for student scores surnamed Li needs to be placed behind the where clause select *, chinese + math + english from student where name like 'Li % 'order by chinese + math + english; total function count the total number of students in a class? Select count (*) from student; how many students with scores greater than 90? Select count (*) from student where math> 90; what are the total number of people with a total score greater than 230? Select count (*) from student where chinese + math + english> 230; sum calculates the total mathematics score of a class? Select sum (math) from student; calculate the total scores of all chinese, english, and mathematics subjects in a class: select sum (math), sum (chinese), sum (english) from student; calculate the sum of the chinese, english, and mathematics scores of a class. select sum (math + chinese + english) from student; calculate the average chinese score of a class. select sum (chinese)/count (*) from student; those absent from the test do not participate in the calculation of select sum (chinese)/count (chinese) from student; avg chinese average select avg (chinese) from student; max/min select max (chinese) from student; select statement (6) grou P by order table create table orders (id int, product varchar (20), price float); insert into orders (id, product, price) values (1, 'TV ', 900); insert into orders (id, product, price) values (2, 'Washing Machine ', 100); insert into orders (id, product, price) values (3, 'washing powder ', 90); insert into orders (id, product, price) values (4, 'Orange', 9); insert into orders (id, product, price) values (5, 'laundry detergent ', 90); select * from orders group by produc T; select *, sum (price) from orders group by product; select *, sum (price) from orders group by product having sum (price)> 100; // sort commodities with a unit price greater than 20 and display the items sorted by price select * from orders where price> 20 group by product order by price; 5. when defining a table, we can add constraints to the field while creating the table, restrictions on the data to be inserted in the future I. constraints on the table's constraint table is to add some constraints to the fields in the table when defining the table, limits the data to be inserted in the future. unique Constraint unique create table a (id int, username va Rchar (20) unique, password varchar (20); insert into a (id, username, password) values (1, 'hangsan ', '123 '); insert into a (id, username, password) values (2, 'hangzhou', '123'); 2. non-null constraint not null create table B (id int not null, name varchar (20); insert into B (id, name) values (1, 'AAA '); insert into B (id, name) values (1, 'bbbbb'); 3. a primary key constraint is equivalent to a unique constraint + a non-empty constraint. each table in the database must have at least one primary key, usually id create table c (id int Primary key, name varchar (20); insert into c (id, name) values (1, 'AAA'); insert into c (id, name) values (2, 'bbbbb'); create table d (firstname varchar (20), lastname varchar (20), primary key (firstname, lastname); insert into d (firstname, lastname) values ('Tom ', 'cat'); insert into d (firstname, lastname) values ('Tom', 'hks '); create table e (id int, name varchar (20); insert into e (id, nam E) values (1, 'AAA'); add the primary key constraint alter table e add primary key (id) to the table; modify the command here: modify the job column, set the length to 60. Alter table employee modify job varchar (60); delete the primary key constraint alter table e drop primary key; 4. the function of defining automatic growth of the primary key is to make the id key automatically grow as the entry increases. Note that the primary key type must be int. only int type can be used to create table f (id int primary key auto_increment, name varchar (20); insert into f (name) values ('aaa'); insert into f (id, name) values (11, 'BBB '); mysql> select * from f; + ---- + ------ + | id | name | + ---- + ------ + | 1 | aaa | 2 | aaa | 3 | aaa | 4 | aaa | 11 | bbb | | 12 | aaa | 13 | aaa | 14 | aaa | 15 | aaa | 16 | aaa | + ---- + ------ + foreign key constraint binding force: the inserted foreign key value must be the value in the referenced column. the referenced data in the referenced table cannot be deleted. note: the foreign key constraint is not empty and is unique binding force to create the husband table and wife table create table husband (id int primary key auto_increment, name varchar (20 )); create table wife (id int primary key auto_increment, name varchar (20), husbandid int, constraint husbandid_FK foreign key (husbandid) references husband (id); external constraints: constraint defines foreign key constraints. foreign key is the foreign key name. add two records, insert into husband (name) values ('laobi'), respectively, based on the primary key of a table '); insert into husband (name) values ('laoyu'); insert into wife (name, husbandid) values ('fengjie', 2); insert into wife (name, husbandid) values ('furongjie', 1); fengjie: select * from husband where id = (select husbandid from wife where name = 'fengjie '); delete from husband where name = 'laoyu'; relationship between tables 6 1 multiple-to-one create department table create table department (id int primary key auto_increment, name varchar (20 )); add information about three departments: insert into department (name) values ('dashboard '); insert into department (name) values ('sales department'); insert into department (name) values ('personnel authorization'); create table employee (id int primary key auto_increment, name varchar (20), inclumentid int, constraint inclumentid_fk foreign key (inclumentid) references department (id); add six employee information: insert into employee (name, employee mentid) values ('employee 3', 1); insert into employee (name, employee mentid) values ('li Si', 1); insert into employee (name, inclumentid) values ('Wang Wu', 2); insert into employee (name, inclumentid) values ('Zhao Liu', 3); insert into employee (name, 1_mentid) values ('Tian 7', 3); insert into employee (name, 1_mentid) values ('week 8', null); multi-table queries show all employees in Department 1 select * from employee where employee mentid = 1; select * from employee where employee mentid = (select id from department where name = 'demo '); find out the select * from department where id = (select employee mentid from employee where name = 'Zhao Liu'); -------------------------------------------- select * from department, employee; + ---- + -------- + ---- + ------ + -------------- + | id | name | departmentid | + ---- + -------- + ---- + ------ + -------------- + | 1 | Development Department | 1 | zhang San | 1 | 2 | sales department | 1 | Zhang San | 1 | 3 | personnel department | 1 | Zhang San | 1 | 1 | Development Department | 2 | Li Si | 1 | 2 | sales Department | 2 | Li Si | 1 | 3 | personnel department | 2 | Li Si | 1 | 1 | Development Department | 3 | Wang Wu | 2 | 2 | sales department | 3 | Wang Wu | 2 | 3 | personnel department | 3 | Wang Wu | 2 | 1 | Development Department | 4 | Zhao six | 3 | 2 | sales department | 4 | Zhao six | 3 | 3 | personnel department | 4 | Zhao Liu | 3 | 1 | Development Department | 5 | Tian Qi | 3 | 2 | sales department | 5 | Tian Qi | 3 | 3 | personnel department | 5 | Tian Qi | 3 | 1 | Development Department | 6 | Week 8 | NULL | 2 | sales department | 6 | Week 8 | NULL | 3 | personnel department | 6 | Week 8 | NULL | + ---- + -------- + ---- + ------ + -------------- + arrangement and combination of all records of multiple tables in the Cartesian set many pieces of matched data (refer to the values of foreign key columns of the table and the primary key of the referenced table)) in order to facilitate the removal of the waste data plus conditions, select * from department, employee where employee. required mentid = department. id; add a condition to query select * from department, employee where employee. required mentid = department. id and department. name = 'dgeta'; optimized to add an alias to the table and only retain the required data select e. * from department d, employee e where e. required mentid = d. id and d. name = 'dashboard '; find Zhao 6 in that department select d. * from department d, employee e where e. required mentid = d. id and e. name = 'Zhao Liu'; 2. create table teacher (id int primary key auto_increment, name varchar (20); add three teachers insert into teacher (name) values ('olde '), ('olde'), ('olde'); create table student (id int primary key auto_increment, name varchar (20); add three students insert into student (name) values ('Taobao'), ('ermao '), ('San Mao'), ('Four hairs '), ('five hairs'), ('six hairs '), ('seven Mao '), ('Eight Mao'), ('small Mao '); create an intermediate table description relation create table tea_stu (teaid int, stuid int, primary key (teaid, stuid), constraint into foreign key (teaid) references teacher (id), constraint stuid_FK foreign key (stuid) references student (id); add data insert into tea_stu (teaid, stuid) values ), ); query the select s students taught by the instructor on the 2 Th. * from student s, tea_stu ts where ts. stuid = s. id and ts. teaid = 2; query the select s of all students taught by the old master. * from student s, tea_stu ts, teacher t where ts. stuid = s. id and ts. teaid = t. id and t. name = 'olde '; the results of the multi-table joint query are n-1 conditions required for n tables in the Cartesian set for joint query. the condition for removing waste data is referred to the foreign key column of the table = the primary key of the referenced table. add the query condition to the column to obtain result 3. one-to-one create table person (id int primary key auto_increment, name varchar (20); create table idcard (id int primary key, location varchar (20 ), constraint personid_FK foreign key (id) references person (id); insert into person (name) values ('zhangsan'); insert into person (name) values ('lisi '); insert into idcard (id, location) values (2, 'Tianjin '); insert into idcard (id, location) values (1, 'Shanghai '); check Li Si's ID card select idcard. * from person, idcard where idcard. id = person. id and person. name = 'lisi'; bitsCN.com