3. mysql Chinese problems, database-level operations, table-level operations, data CRUD, grouping operations, time and date, string-related functions, and table Constraints

3. mysql Chinese problems, database-level operations, table-level operations, data CRUD, grouping operations, time and date, string-related functions, and table Constraints
Bytes

1. Connect to the MYSQL server: mysql-uroot-p123456

1. View Chinese problems

   Show variables like 'character % ';

    

   2. Modify the mysql Character Set and exit the mysql prompt page:

   Mysql-uroot-p -- default_character_set = gbk;

    

   3. Database Operations: Create, view, modify, and delete databases.

   * Creation:

   Create a database named mydb1.

   Createdatabase mydb1;

   Create a mydb2 database using the UTF-8 character set.

   Create database mydb2character set utf8;

   Create a mydb3 database that uses the UTF-8 character set and has verification rules.

   Createdatabase mydb3 character set utf8 collate utf8_general_ci;

   * View:

   Show all databases

   Showdatabases;

   Displays the statement information for creating a database.

   Showcreate database mydb2;

   * Modification:

   Modify the character set of mydb1 to gbk (the database name cannot be modified)

   Alterdatabase mydb1 character set utf8;

   * Delete:

   Delete database mydb2

   Dropdatabase mydb1;

    

   4. Table operations: Create, view, modify, and delete tables.

   Usemydb2;

   * Creation:

   Create Table Person based on object class person

   Person {

   Intid;

   Stringname;

   }

   Createtable person (

   Idint,

   Namevarchar (20)

   );

   Data Type in mysql:

   Bit1, but the number of digits can be specified, such as bit <3>

   Int2 bytes can specify the maximum number of digits, for example, int <4> an integer with a maximum of four digits.

   Float2 bytes can specify the maximum number of digits and the maximum number of decimal places, for example, float <5, 2> can be a maximum of five digits, with a maximum of two decimal places

   You can specify the maximum number of digits and the maximum number of decimal places for a double byte. For example, float <6, 4> can be a maximum of six digits, with a maximum of four decimal places.

   Char must specify the number of characters. For example, if char (5) is an unchangeable character, even if the stored content is 'AB', the data is stored in a space of five characters.

   Varchar must specify the number of characters. For example, if varchar (5) is a variable character, if the stored content is 'AB', it occupies 2 characters. If it is 'abc ', it takes up three characters

   Text: large text (large string)

   Blob: Binary big data, audio, and video files

   Date: for example, '2017-01-02'

   Datetime: Date and time, for example, '2017-01-02 12:23:43'

   TimeStamp: timeStamp, which is automatically assigned to the current date and time.

    

   Create an employee table

   Createtable employee (id int, name varchar (20), sex bit, birthday date, salarydouble, entry_date date, resume text );

   * View:

   View All Tables:

   Showtables;

   View the statement for creating a specified table

   Showcreate table employee;

   Mysql table names are case sensitive

   Display the structure of the specified table:

   Descemployee;

   * Delete:

   Delete an employee table

   Droptable employee;

   * Modify a table:

   Create table worker (id int, name varchar (20), sex bit, birthday date, salarydouble, entry_date date, resume text );

   Add a field: altertable worker add column height double;

   Modify a field: altertable worker modify column height float;

   Delete a field: altertable worker drop column height;

   Change table name: renametable employee to worker;

   Modify the character set of a table: altertable worker character set gbk;

    

   5. CRUD of table data

   * C (add data in create) Insert statement

   Create an Employee table and add some records to the table

   Createtable employee (

   Idint,

   Namevarchar (20 ),

   Sexbit,

   Birthdaydate,

   Salarydouble,

   Entry_datedate,

   Resumetext

   );

    

   Insertinto employee (id, name, sex, birthday, salary, entry_date, resume) values (1, 'zhang san', 1, '2017-09-21 ', 1983, '1970-06-24 ', 'a scale ');

   Insertinto employee (id, name, sex, birthday, salary, entry_date, resume) values (2, 'lily', 1, '2017-09-21 ', 1984, '1970-07-24 ', 'one Cool ');

   Insertinto employee (id, name, sex, birthday, salary, entry_date, resume) values (3, 'wang wu', 0, '2017-09-21 ', 1985, '2017-08-24 ', 'a mavericks ');

   Deletefrom employee where id = 1

    

   Createtable employee (id int, namevarchar (20), sex bit, birthday date, salary double, entry_date date, resume text );

    

   * U (update data) Update statement

   Increase the salaries of all employees by 500 yuan.

   Updateemployee set salary = salary + 500;

   Change the employee salary of Wang Wu to 10000 yuan, and change resume to a normal employee.

   Updateemployee set salary = 10000, resume = 'is also a zhongniu 'where name = 'wangwu ';

   * D (drop to Delete data) Delete statement

   Delete the record with the table name Wang Wu.

   Deletefrom employee where name = 'wang 5 ';

   Delete all records in the table.

   Deletefrom employee; -- conditional deletion is allowed, but the deletion of all records is a little worse.

   Use truncate to delete records in a table.

   Truncateemployee; -- high unconditional Efficiency

    

   6 * R (Retrieve data search) Select statement

   Prepare the environment:

   Createtable student (

   Idint,

   Namevarchar (20 ),

   Chineseint,

   Englishint,

   Mathint

   );

    

   Insertinto student (id, name, chinese, english, math) values (1, 'he dong', 80, 85, 90 );

   Insertinto student (id, name, chinese, english, math) values (2, 'authorization', 90,95, 95 );

   Insertinto student (id, name, chinese, english, math) values (3, 'he nan', 80, 96, 96 );

   Insertinto student (id, name, chinese, english, math) values (4, 'ettan ', 81,97, 85 );

   Insertinto student (id, name, chinese, english, math) values (5, 'heshi', 90 );

   Insertinto student (id, name, chinese, english, math) values (6, 'clove ', 92,85, 87 );

   Insertinto student (id, name, chinese, english, math) values (7, 'hebei', 80 );

   Insertinto student (id, name, chinese, english, math) values (8, 'tangjiao', 79 );

   Insertinto student (id, name, chinese, english, math) values (9, 'renzhi', 85 );

   Insertinto student (id, name, chinese, english, math) values (10, 'wang Yue ', 84 );

    

   Query the information of all students in the table.

   Select * from student;

   Query the names and English scores of all students in the table.

   Selectname, english from student;

   Filter duplicate data in the table.

   Selectenglish from student;

   SelectDISTINCT english from student;

   SelectDISTINCT english, name from student;

   Selectenglish + chinese + math from student;

   Selectenglish + chinese + math as total score from student;

   Selectname, english + chinese + math as total score from student;

   Add 10 points of expertise to the English scores of all students.

   Selectname, english + 10 from student;

   Calculate the total score of each student.

   Selectenglish + chinese + math from student;

   Use aliases to indicate student scores

   Selectname, english + chinese + math as total score from student;

   Selectname, english + chinese + math total score from student;

   Query the score of the student whose name is east

   Select * from student where name = 'he Dong ';

   Query students whose English score is greater than 90

   Select * from student where english> 90;

   Query all students whose total score is greater than 250

   Select * from student where english + chinese + math> 250;

   Query students whose English scores are between and 95.

   Select * from student where english> = 85 and english <= 95;

   Select * from student where english between 85 and 95;

   Students who scored and 91.

   Select * from student where math = 84 or math = 90 or math = 91;

   Select * from student where math in (84,90, 91 );

   Query the scores of all students surnamed he.

   Select * from student where name like 'He % ';

   Query mathematics score> 85, Chinese score> 90.

   Select * from student where math> 85 and chinese> 90;

    

   Sorts mathematical scores and outputs them.

   Select * from student order by math;

   Output after sorting the total score, and then output in the order from high to low

   Select * from student order by math + chinese + english desc;

   Sorting and output of student scores with the surname he

   Select * from student where name like 'He %' order by math + chinese + english desc;

   Selectname, math + chinese + english from student where name like 'He %' order bymath + chinese + english desc;

   How many students are counted in a class?

   Selectcount (*) from student;

   How many students have scored more than 90 in mathematics?

   Selectcount (*) from student where math> 90;

   How many people are there with a total statistical score greater than 250?

   Selectcount (*) from student where math + chinese + english> 250;

   Count the total score of a class's mathematics?

   Selectsum (math) from student;

   Measure the total scores of each class in Chinese, English, and mathematics.

   Selectsum (math), sum (chinese), sum (english) from student;

   Total scores of Chinese, English, and mathematics in a class

   Selectsum (math + chinese + english) from student;

   Selectsum (math) + sum (chinese) + sum (english) from student;

    

   What is the average mathematical score of a class?

   Selectavg (math) from student;

   Calculate the average score of a class total score

   Selectavg (math + chinese + english) from student;

   Selectavg (math) + avg (chinese) + avg (english) from student;

   Calculate the highest score and lowest score of the class.

   Selectmax (math + chinese + english), min (math + chinese + english) from student;

   7. Comprehensive Exercise: Add a class column to the student table and query the training group.

   Find the total score of each class, the highest score

   Prepare the environment

   Add a field to the table: altertable student add column class_id int;

   Update table:

   Updatestudent set class_id = 1 where id <= 5;

   Updatestudent set class_id = 2 where id> 5;

   Selectsum (math + chinese + english), max (math + chinese + english) from student group byclass_id;

   The ID of the class whose total score is greater than 1300 is queried.

   Selectclass_id from student group by class_id havingsum (math + chinese + english)> 1300;

   Selectclass_id from student where sum (math + chinese + english)> 1300 group by class_id;

   Note: difference between where and group: grouping functions cannot be used in wehre clauses.

    

    

    

   8. time and date

   Mysql> select year (now (), month (now (), day (now (), date (now ());

   + -------------- + ------------ + ------------- +

   | Year (now () | month (now () | day (now () | date (now () |

   + -------------- + ------------ + ------------- +

   | 2014 | 9 | 7 |

   + -------------- + ------------ + ------------- +

    

   Selectdate_add (now (), INTERVAL 2 year) from dual; // increase by two years

   Selectcharset ('name') employee;

   Selectdate_add (now (), INTERVAL-1 day) yesterday, now () Today, date_add (now (), INTERVAL + 1 day) tomorrow;

    

   9 string-related functions

   Selectconcat (charset ('name'), 'aaa') custom from dual;

    

    

   10 Table Constraints

   * Define the primary key constraint primarykey: it cannot be blank or repeated

   * Define auto_increment for automatic growth of primary keys

   * Define the unique constraint unique

   * Define non-null constraint notnull

   * Define the foreign key constraint constraintordersid_FK foreign key (ordersid) references orders (id)

   * Delete A primary key: altertable tablename drop primary key;

    

   Createtable myclass

   (

   IdINT (11) primary key auto_increment,

   Namevarchar (20) unique

   );

   Createtable student (

   IdINT (11) primary key auto_increment,

   Namevarchar (20) unique,

   Passwdvarchar (15) not null,

   ClassidINT (11), # note that do not use commas

   Constraintstu_classid_FK foreign key (classid) references myclass (id)

   );